Formatting date with inconsistent input data in same column

[Anonymous]

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

[Anonymous]

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

[mussaenda]

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"

[amitchandak]

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

[Anonymous]

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.

.

[mussaenda]

Hi @Anonymous ,

I disagree.

Mine has this one.

I am using English UK as default language for the report.

[Anonymous]

Hi @mussaenda,

Unfortunately I do not share that luxury... Funny how it even teases me with the two US options. Also, I don't have admin privileges to change my OS system settings from US to UK.

Either way it wouldn't change the formatting issue but I would prefer dd/mm/yy

[Anonymous]

Hi @Anonymous ,

You can try to change column type with 'using locale' option, it allow you to setting specific data type region to help power bi analysis records:

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}},"en-gb")

Local date formats in Power BI

Regards,

Xiaoxin Sheng

[Anonymous]

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