Forum

Notifications
Clear all

Power Query, Power Pivot Date-Grouping Errors

2 Posts
2 Users
0 Reactions
136 Views
(@ngoanyanwugmail-com)
Posts: 1
New Member
Topic starter
 

Dear Mynda,

My question is on an excel file I downloaded from one of your webinars on

https://www.myonlinetraininghub.com/excel-dashboards-using-power-query-power-pivot-webinar-replay-downloads

The source document has wrong date formats that only recognises the DD as the month(MM) and recognises the month (MM) as the DD when grouped. For the entire data of 22908 rows with complete months from Jan - December, my Pivot could only covert to Jan and Quarter 1 when grouped in month, quarter and year, I have tried everything possible from text to columns to custom formating, nothing seems to work.

The file is attached for your perusal.

 
Posted : 13/05/2020 8:44 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Ngozi,

Welcome to our forum!

The data isn't in the wrong format. It's just that your PC uses mm/dd/yyyy and mine uses dd/mm/yyyy. This is a common issue and one you can fix using Power Query to 'change type' > using locale > choose 'Date' and 'English (Australia)'. Note: you should remove the first 'Changed Type' step in the query before using 'Change type' > using locale.

Hope that points you in the right direction.

Mynda

 
Posted : 13/05/2020 11:09 pm
Share: