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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors