Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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?

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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