Serial date from Excel not converting to date

[johnf]

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?

[rocky09]

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]

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]

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

[johnf]

Sure @MarcelBeug. Please find screenshots below:

Excel datasetPower BI Query Editor ImportPower BI error message

[MarcelBeug]

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 · Accepted solution]

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