Forum Discussion

johnf's avatar
johnf
Helper I
9 years ago
Solved

Serial date from Excel not converting to date

Hi.

 

I'm importing an Excel file that I have to do a lot of transformation to.

 

The problem is buried in the columns of the imported data is dates that have been used as the column headers. Unfortunately on import it brings them in to a row with other headers in that row and has converted them to the serial date that is used by Excel in text.

 

After transforming to get the dates in a column and change the data trype to "Date" I get error "DataFormat.Error: We couldn't parse the input provided as a Date value."

 

How can I convert the serial date in to a normal DD/MM/YYYY format?

Need Help
  • johnf's avatar
    johnf
    9 years ago

    Thanks MarcelBeug.

     

    Apologies I thought I had included it in the post, please find below:

     

    let
    Source = Excel.Workbook(File.Contents("####.xls"), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),

    But seeing your test M code has helped me isolate the issue as mine was auto converting all columns to type "text" and not type "any" as per your example. Even trying to manually change the "type text" to "type any" in the M code changed the column data type but didn't convert the serial to date.

     

    It appears to have something to do with the version the Excel spreadsheet is in as it's saved as XLS. I converted it to an XLSX file and now I get the following M code on import:

     

    let
    Source = Excel.Workbook(File.Contents("####.xlsx"), null, true),
    Sheet2 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}}

    And it's now autodetecting the dates correctly

     

    Dates rendered correctly

    Thanks for your help.

     

    Kind regards,

    John

6 Replies

  • rocky09's avatar
    rocky09
    Solution Sage
    9 years ago

    I beleive, you need to change the format first in Excel and try to get data again. Make sure, the date data doesn't contains any spaces/special characters.

    • johnf's avatar
      johnf
      Helper I
      9 years ago

      Thanks rocky09 the format is already set as short date in Exceland there is no special characters. All the dates render correctly in Excel.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Without screen shot and (the relevant small part of) your code it's rather impossible for me to paint the picture myself.