Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Formatting date with inconsistent input data in same column

Hi all,

 

I'm having trouble formatting a column of date data which has two date formats from the raw data.

 

The input data has a mixture of d/m/yyyy and dd/mm/yyyy

For example there is 2/8/2019 and 13/08/2019

 

Power BI appears not to be able to parse the latter format from text into date format, but can for the former. Below is the error that is returned:

[DataFormat.Error] We couldn't parse the input provided as a Date value.


Could anyone please direct me to a method to clean and transform the data to create a regular, recognisable date format?

Thanks a bunch in advance for your help.

Josh

 

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    In the modeling tab in Model view, is it allowing you to change Format:dd/mm/yyyy?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      There is the option for dd-mmm-yyyy and mm/dd/yy but there is not option for dd/mm/yyyy.

       

      I.e. there is not option to have full number date format with day first.

       

      To add some clarity to the issue in the image below I've duplicated the column, leaving the first column in text/num format and second in date format (mm/dd/yy) using the modeling ribbon rather than in the query editor.


      As you can see it recognoises the ordering on month and day changes depending on whether the original column uses 8 or 08 for the month value.

       

       

      .

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Also this is what the data looks like in Excel. You see some recognised dates and some as text

         

  • mussaenda's avatar
    mussaenda
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    I cannot replicate your issue.

    i tried these two dates and they can be formatted in power query. check this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWN7DQNzIwtFSK1YlWMtKHcWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}})
in
    #"Changed Type"
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Solution

     

    I ended up using the split column function in query editor, split by delimeter "/". The saving grace is that the data is at least always in day/month/year format. So then I have three columns and merge these in a calculator column using the date function.


    It works, if somewhat clunky. Feel free to let me know of any better ideas.

    Cheers
    Josh