cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eritter
Resolver II
Resolver II

Stuck on a date issue

I've done an import from Salesforce and unfortunately, SF is sending over dates with multiple formats for the same column - as Text.  In Excel, this is fixed by simply selecting the entire column, doing a Text to Columns, Select Delimited, Next, No Options checked, Next, Column Date Form to Date: MDY, and presto, all is well.  In PowerBI, I can convert the column to Date, but then many of the cells end up showing an error.  The text of the error is

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    22/03/2018

 

Is there a way to do the same type of convert that the Text To Columns Excel feature does - or better, is there a way to identify more about what is causing the error?

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @eritter,

 

After loading the date field into desktop, please refer to below steps to convert its date type.

 

Open Query Editor. Split column by delimiter "/".

1.PNG

2.PNG

 

Add a custom column to combine them with a supported format.

=Text.Combine({Number.ToText([Date.2]),Number.ToText([Date.1]),Number.ToText([Date.3])},"/ ")

3.PNG

 

Set the data type for custom column to Date.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Microsoft
Microsoft

Hi @eritter,

 

After loading the date field into desktop, please refer to below steps to convert its date type.

 

Open Query Editor. Split column by delimiter "/".

1.PNG

2.PNG

 

Add a custom column to combine them with a supported format.

=Text.Combine({Number.ToText([Date.2]),Number.ToText([Date.1]),Number.ToText([Date.3])},"/ ")

3.PNG

 

Set the data type for custom column to Date.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.