Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 🙂

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@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))

c1.JPG

 





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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
tarunsingla
Solution Sage
Solution Sage

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
Not applicable

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

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

DateParse.png

 

Regards,

Tarun


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

ryan_mayu
Super User
Super User

@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))

c1.JPG

 





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

Proud to be a Super User!




Anonymous
Not applicable

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
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.