Power Query to Pivot Table / Incorrect Date Format

[radonwilson]

Using power query, I changed this date & time format 01-Nov-2022 2:18:19 pm UTC to this 01-11-2022 date format.
And once, I loaded this query data to the Pivot table I got the date formatted as 44866 (a 5-digit number).

I want dd/mm/yyyy date format and I also tried to get the desired format by right-clicking one of the date field items in the pivot table and changing its format to dd/mm/yyyy but It did not update all the cells.

Why its happening and how to fix?

 

[alansidman]

Looks like you just need to reformat your Date Column in the PT to a date format. It appears to have reverted to its native format.

 

[radonwilson]

I have done the same but its rolls back to the native format each time PT changes.

Looks like you just need to reformat your Date Column in the PT to a date format. It appears to have reverted to its native format

 

[jdellasala]

Did you load the query to the Data Model and then build the PT from that? If so, you need to fix the format of the Date column in the Data Model as well as the format.

It shouldn't be needed if the data type is Date from PQ, although the default Date format in the DM is Date Time AM/PM and was changed above. Also, are you using a Calendar Table? Are there other Tables involved?

 

[GraH]

Hard one and I can't simulate it.
So here is a shot in the dark. Reload your query to a connection only. Save the workbook. Then reload to the pivot table. Would that change anything?

 

[radonwilson]

Hard one and I can't simulate it.
So here is a shot in the dark. Reload your query to a connection only. Save the workbook. Then reload to the pivot table. Would that change anything?

Thanks for your solution, but it didn't work.

 

[radonwilson]

Did you load the query to the Data Model and then build the PT from that? If so, you need to fix the format of the Date column in the Data Model as well as the format.
View attachment 80542
It shouldn't be needed if the data type is Date from PQ, although the default Date format in the DM is Date Time AM/PM and was changed above. Also, are you using a Calendar Table? Are there other Tables involved?

No, I didn't load the query to the data model and then made PT. I made PT from the PQ only.
The date format is alright in the power query. (refer to screenshots in my first message).

I tried a few solutions to fix it but I failed. So finally I decided to change the date format manually by selecting the whole column on the sheet and changed it to the desired date format.

But each time, I am changing the pivot table filter value, the date format is rolling back to 44896.00
this format. It's very annoying.

 

[datatronics505]

Another shot in the dark: open your query for edit and choose to "Change From Locale" when changing the data type of the date column (left corner of that column's title bar and choose the bottom option of the drop down menu "Change from Locale").