Stuck on a date issue

[Anonymous]

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?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

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

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

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])},"/ ")

Set the data type for custom column to Date.

Best regards,

Yuliana Gu