Change dates from european system to the US

[jazzista]

I am trying to change the dates from DAY/MONTH/YEAR to MONTH/DAY/YEAR in power query however when i upload the file, the dates are treated as text and some are treated with errors. Can the LOCALE function be used here to change the dates? What is the M code for this? I highlighted the date column that needs to get transform
Thanks again in advance.

 

[Kaso]

Hey there,

i've had a look and you do get the errors thanks to your different date formats.
Excel makes the ones with dot to datetime and the ones with slash just to date.
That means in order to work with your dates, you just have to get that in to one consistent format.

I could make that on your file, but i think you're able to do that yourself
Only one problem at the end, if you use power query, the output table will not have your javaScript links or whatever it is :/

 

[jazzista]

HI kaso: good morning. Thanks for replying: The only way I got it to work was that prior to upload it into the get and transform I added the " ' " symbol in the excel sheet and I was able to bring up all the dates as text and then I did the transformation inside PQ using the / symbol to split columns and created a custom column to get the dates right. I was just curious to see if the transformation could be done inside PQ in a more efficient way

 

[Kaso]

Here is what i did, which i think is pretty much the same thing you do have.

View attachment Clean trial.xlsx

I'm sorry, i cannot think of an other way right now, but one of the pro's like MarcelBeug will bring something better im sure of it

 

[jazzista]

Hi Kaso: What a great solution. I will try to replicate it by following the applied steps: The one thing I like is that you do all the transformation inside power query. Many Thanks