Power Query to Pivot Table / Incorrect Date Format

radonwilson

Board Regular
Joined
Jun 23, 2021
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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?

Screenshot (27)1.jpg
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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
 
Upvote 0
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.
1670605254129.png

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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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").
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,815
Members
452,426
Latest member
cmachael

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top