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
= 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 🙂
Solved! Go to 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))
Proud to be a Super User!
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!
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:
Regards,
Tarun
Did I answer your question? Mark my post as a 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))
Proud to be a Super User!
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:
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"
User | Count |
---|---|
114 | |
61 | |
59 | |
41 | |
39 |
User | Count |
---|---|
118 | |
66 | |
66 | |
65 | |
48 |