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.
Hi. I have original data from Mailchimp for "Send Time" which is not formated as date, and has values like: "2019-05-05T13:24:46+00:00"
I can't format this as a date, I get an #Error (it's currently text.)
I have a new colum called Date Sent, which works with this formula:
In case someone has the same problem and finds this thread: in Power Query there is a data type Date/Time/Timezone. Selecting the column and applying this data type did the trick for me.
That format is ISO8601 and is fairly standard and is recognised by Power BI.
Not sure if this will help but... when I enter that value in an "Enter Data" new query it automatically translates it into a date and provides this as the steps it takes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAWiEENjKyMTKxMzbQMDKwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Mailchimp_Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Mailchimp_Date", type datetime}})
in
#"Changed Type"
"i45WMjIwtNQ1MAWiEENjKyMTKxMzbQMDKwMDpdhYAA==" is the binary encoding for your value "2019-05-05T13:24:46+00:00"
Could it be the double quotes around the value that is making Power BI think it is text? Can you strip them out?
You should use Power Query.
Change it to datetime.
Then, in a second step, change it to date.
Hey, did you get a solution. I am having the same issue.
Thanks
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |