Hi, I've just joined the Power Query course and only discovered Power Query a few weeks ago. I'm no expert at Excel but trying..
I have a question that probably most will find very basic but here it is:
I have imported a file into Power Query and successfully manipulated/cleaned up my data...to a point.
I'm having problems with the first column (see attached) which is meant to be a Date column but it's in Text format.
The issue I have is - the text format of the date is in MM/DD/YYYY but I need it to be DD/MM/YYYY.
I tried splitting the column via delimiters, then swapping the Month & Day columns, then merging back together but when I change the data type to "Date", numerous data will go into error and I believe it's due to the Month only having 1 numeral eg 4th May 2018 (04/5/2018)
I was thinking I could just accept it being in a "text" format but I thought it may cause issues down the track when trying to create pivot tables and add slicers to do with Dates etc...
Apologies if this is very straight forward...
Your help would be greatly appreciated
Regards,
Sarah (Australia)
Hi Sarah,
You need to 'Change Type Using Locale' as covered in session 4.03 of the Power Query course. The locale will be English (United States).
Kind regards,
Mynda
Hi Mynda,
Thanks very much for replying and your suggestion. I've given that a go and some dates still go into error and I have no idea why.
I've attached the file again after the "Change Type Using Locale". Some dates worked perfectly but not others...
Cheers
Sarah
Hi Sarah,
I can't use your file because the connection is external to the file and I don't have access to the original data. When I used the data loaded into the table by Power Query all of the dates converted correctly using 'Change Type Using Locale'.
You need to change the date type at the very first 'Change Type' step (the 3rd step in the applied steps pane), not after all of the other transformations you've done. This is because you need to replace the original change type step as opposed to changing something that is already changed. Just select the 3rd step for Changed Type then perform the 'Change Type Using Locale' > choose 'replace the original change type'.
If you're still stuck please post some sample dates in an CSV or Text file so that it retains the original mm/dd/yyyy format. I can then test it properly.
Mynda
