Converting XML Datetime (e.g. 2018-03-04T14:24:43-08:00) to datetime in PowerBI

[Anonymous]

I'm completely new to PowerBI so maybe this will be easy.

I have imported my dataset from SQL, which contains a row with XML Datetimes. PowerBI wont recognize this coloumn(Interchange[DateTime]) as datetime. So how can I convert this? I have tried my way around a DAX formular and a Power Query to add an extra coloumn to my dataset with the correctly formatted date. But I think I cannot get the syntax right.

DAX attempt

FormattedDate = FORMAT(Interchange[DateTime];"dd-MM-yyyy")

-> Result is that the new coloumn still shows XML Date - identical to the original.

PowerQuery Attempt

 = Table.AddColumn(#"Removed Columns", "Custom", each DateTime.FromText([DateTime], "dd-MM-yyyy"))

-> Result DataFormat.Error: We couldn't parse the input provided as a DateTime value. Details: 2018-03-04T14:24:43-08:00

Any help is appreciated, thanks 🙂

[tarunsingla]

Your Power Query Attempt failed because the input date is not in the format accepted by the DateTime.FromText function.

Check the supported formats, for that specific function.

https://docs.microsoft.com/en-us/powerquery-m/datetime-fromtext.

Your DAX attempt failed probably because of cautions/limitations for the FORMAT function as listed here:

https://docs.microsoft.com/en-us/dax/format-function-dax

To get the desired result, you should try to Parse the date instead of trying to change the data type of the Date field.

Regards,

Tarun

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks - If you could share a solution where we pass the date, that would be great, because I havent been able to so far 😕

[tarunsingla]

Date Parse is readily available in Power BI Query Editor. You do not have to hand-craft the query. Try this:

Regards,

Tarun

Did I answer your question? Mark my post as a solution!

[ryan_mayu · Accepted solution]

@Anonymous

You can also use DAX to create another column to get the date. Hope this is helpful. Thanks.

date = DATE(left(Table1[Column1],4),MID(Table1[Column1],6,2),MID(Table1[Column1],9,2))

[Anonymous]

The only way I could figure out this parsing was to use the substring method you suggested, thanks. 

The final formula for parsing date e.g. 2018-03-04T14:24:43-08:00 into a datetime format was:

Formatteddate = DATE(left(Interchange[DateTime];4);MID(Interchange[DateTime];6;2);MID(Interchange[DateTime];9;2)) + TIME(mid(Interchange[DateTime];12;2);MID(Interchange[DateTime];15;2);MID(Interchange[DateTime];18;2))

[Greg_Deckler]

That's weird, when I pasted that into an Enter data query, it converted it automagically. Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MNY1MAkxNLEyMrEyAbItrAwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}})
in
    #"Changed Type"