Forum Discussion

Unicorn_Tech's avatar
Unicorn_Tech
Icon for Resolver I rankResolver I
6 years ago

Converting Mailchimp Date to an actual date

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!
Data Modeling
Formatting
mailchimp

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You should use Power Query.

    Change it to datetime.

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

  • cpearson's avatar
    cpearson
    Icon for Resolver I rankResolver I
    6 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.