Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Thanks for your help.
Kind regards,
John
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.
Thanks @rocky09 the format is already set as short date in Exceland there is no special characters. All the dates render correctly in Excel.
Without screen shot and (the relevant small part of) your code it's rather impossible for me to paint the picture myself.
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"
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
Thanks for your help.
Kind regards,
John
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |