Files were created in Excel 365
Workbook A is the data source. It contains a table with a date column.
Data validation restricts the field to dates – there are no strings which only look like dates.
(Some values do also have times, but that’s not relevant)
Workbook B has a Data Connection to the table in Workbook A, so my manager can slice, dice, and aggregate the info in Workbook A.
Here are some of the relevant settings:
External Data Properties:
- Preserve cell formatting is checked
- Preserve column sort/filter/layout is NOT checked
(we have issues with people filtering the source file which we do NOT want reflected here) - For number of rows changing upon refresh, I’ve tried both Insert and Overwrite
Power Query Editor explicitly sets that column to type date.
In the workbook, I have selected the entire column and set the number format to a date format.
Also, File > Options > Advanced > Display options > “Group dates in the AutoFilter menu” is checked.
ISSUE 1: DATE FORMATS
When I refresh the query, new rows display the date serial numbers in General format instead of a date format.
ISSUE 1B: DATE FILTER
When I click the filter button on the top of the column, Excel has the formatted dates grouped by year/month, followed by the date serial numbers
ISSUE 2: IN PIVOT TABLES, DATES WON’T GROUP
Even if I fix the number format for the column, the dates won’t group AT ALL.
- Not when I try to filter them in the Field List
- Not when I try to display them as rows.
