Log In
Register
Home
Active Member
December 10, 2020
Offline
Hi Mynda,
I was trying to replicate to create the HR Interactive Dashboard and i encountered the issue of the pivot table not picking up all the CSV. file data from HR Data folder. Currently it picks up only the 1Qtr, Jan data only as shown below
Attaching the steps that i followed in Word document and my excel practice file,
Grateful if i have missed out any steps or is it because of any loading error? or did i need to click any other option other that "Combine & Transform"
John
See Answer
July 16, 2010
Offline
Hi John,
The problem is caused by the locale difference between your PC and the source data. Your date format is mm/dd/yyyy and the data is dd/mm/yyyy. You need to go back into the query and remove the first changed type step. Then right-click on the date columns > change type > using locale...
In the dialog box choose date and locale 'English (Australia)'. Repeat for the HireDate column.
Mynda
Active Member
December 10, 2020
Offline
Thanks Mynda. It worked. But only after changing all the fields in the power query to "English (Australia)" including the text fields etc. Now i can continue my lessons. Btw, do you have any lessons on "Data Tables"
John
July 16, 2010
Offline
You only needed to use the Local option for the date columns. The others could just have their data type set using 'detect data type' on the Transform tab of the ribbon. It doesn't really matter now, but that method will be much quicker next time.
Answers Post
1 Guest(s)
