Forum Discussion

Grivil's avatar
Grivil
Regular Visitor
3 years ago

Three Different Date Formats in a Column

Hi everyone,    I'm trying to load a pretty large csv file  (~400mb) into Power BI. I encontered that my data has different formatting for a specific row. Is there a way for me to make the data uni...
Need Help
Rickmaurinus's avatar
Rickmaurinus
Helper V
3 years ago

Hi Grivil,

 

So your import has different formats. If you have a mix between

dd-mm-yyyy

mm-dd-yyyy

yyyy-mm-dd

yyyy-dd-mm

 

Unfortunately that's problematic. Let's say you have the value:  06-01-2023

 

In those cases power query can not decide whether a value is: 

6 january 2023

1 June 2023

 

You'll find that there are different formats to extract a date from a value. You probably want to make use of the Date.FromText function and specify a format code as shown here: https://powerquery.how/date-totext/

 

But inconsistent formatting is hard to overcome unfortunately. Can you please check whether it is really that inconsistent? 

 

Cheers,

Rick

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.