Three Different Date Formats in a Column

[Grivil]

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 uniform?

here are the data formatting differences I'm seeing:

Thanks in advance!

[Rickmaurinus]

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.

[ronrsnfld]

• Get Data from Text/CSV
• Transform Data
• In Power Query, right-click on the date column
  • Change Type
    • Using Locale
      • Select something like "English (Europe)"
• Then you can load it back to Power BI

If that doesn't work, please open the CSV file in a text editor like Notepad or Notepad++, and copy/paste some representative data as text. Be sure to obfuscate any confidential information

[Grivil]

i tried that already, excel is giving mw a hard time editing the data as well. 

I don't have access to my device right now, but the screenshot i showed you is the actual data. It's basically timestamps. With different formating for certain dates. 

[ronrsnfld]

What you are showing is NOT the actual data. What you are showing is the result of opening the csv file in Excel. But if you are unwilling to show the actual data I will not be able to assist.

[Mahesh0016]

@Grivil you should contain the date format "dd-mm-yyyy" at the loading time and after loading you can format it as per your requirement.Thank You!!