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. Unf...
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

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.

johnf's avatar
johnf
Helper I
9 years ago

Sure MarcelBeug. Please find screenshots below:

 

Excel datasetPower BI Query Editor ImportPower BI error message

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    The relevant part of your code is still missing...

     

    If I try with a small sample of your sample, it works fine (see screenshot above).

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Serial date from Excel not converting to date.xlsx"), null, true),
    Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}})
in
    #"Changed Type"
    • johnf's avatar
      johnf
      Helper I
      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