Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Anonymous
Not applicable

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
Resolver I
Resolver 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors