cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Unicorn_Tech
Resolver I
Resolver I

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!
4 REPLIES 4
machtwerk
New Member

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. 

cpearson
Advocate I
Advocate I

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?

 

SPG
Resolver IV
Resolver IV

You should use Power Query.

Change it to datetime.

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

Anonymous
Not applicable

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

Thanks

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors