Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |