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

here are the data formatting differences I'm seeing:

 


Thanks in advance!

 

Need Help

5 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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!!

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago
    • 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's avatar
      Grivil
      Regular Visitor
      3 years ago

      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's avatar
        ronrsnfld
        Super User
        3 years ago

        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.

  • 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.