topic Re: Converting Mailchimp Date to an actual date in DAX Commands and Tips

Converting Mailchimp Date to an actual date

Unicorn_Tech — Thu, 28 Nov 2019 09:07:39 GMT

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:

Date Sent = left('Campaign Summary'[send_time],10)

which at least gets the relevant characters, which still can't be formatted as a date (#Error). I've tried changing the format in the "Modeling" tab. I've also tried doing a third column (I know this is silly) which would do this:

format date sent = format('Campaign Summary'[Date Sent],"MM/YY") - which still returns only text.

I only want this to customize my table for relative date filtering, which it can't do on text. Another option is to sort it by the text (which at least shows the most recent at the top), but then to limit the table to only the top ten records.

Please let me know if there is a solution here. Thank you!

Re: Converting Mailchimp Date to an actual date

Anonymous — Wed, 18 Dec 2019 11:30:26 GMT

Hey, did you get a solution. I am having the same issue.

Thanks

Re: Converting Mailchimp Date to an actual date

Anonymous — Wed, 18 Dec 2019 13:51:22 GMT

You should use Power Query.

Change it to datetime.

Then, in a second step, change it to date.

Re: Converting Mailchimp Date to an actual date

cpearson — Wed, 18 Dec 2019 13:54:20 GMT

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?

Re: Converting Mailchimp Date to an actual date

Anonymous — Mon, 20 Apr 2020 12:14:56 GMT

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.