Forum Discussion
Power Query not recognizing header row in some of the multiple excel file from SharePoint folder
Here's a brute force version
let Source = Folder.Files("C:\Users\Lutz\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx" and Text.StartsWith([Name], "Output"))), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each Table.SelectColumns(Table.PromoteHeaders(Table.Skip(Excel.Workbook([Content]){0}[Data], if Excel.Workbook([Content]){0}[Data]{2}[Column3]="Date" then 2 else if Excel.Workbook([Content]){0}[Data]{1}[Column1]="Date" then 1 else 0 ), [PromoteAllScalars=true]),{"Date", "Count", "Header", "Value"})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Date", "Count", "Header", "Value"}, {"Date", "Count", "Header", "Value"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Date", type date}, {"Count", Int64.Type}, {"Header", type text}, {"Value", Currency.Type}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Date] <> null)), #"Replaced Errors" = Table.ReplaceErrorValues(#"Filtered Rows1", {{"Count", 0},{"Value", null}}) in #"Replaced Errors"but this could be refined if you have more than three scenarios.
Create a custom function that calls Table.ColumnNames and then adds the processing steps if the first returned value is "Column1"
Thank you for your suggestion.
Will Custom Functions be a workable solution if the headers starts on the 3rd column and 3rd row of the excel file?
yes. You could even have code that hunts for the first filled row or column, but ideally your source format (how quirky it may be) should be reasonably stable.
if you want you can post some sample files. If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
I included the sample files in hyperlinked within the text. Thank you so much for your assistance.
Here would be the general approach - read the files through a folder connector and then interpret each file content with the template code. Then combine the results and clean up (your sample data is rather bumpy...)
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx" and Text.StartsWith([Name], "Output"))), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each Table.SelectColumns(Table.PromoteHeaders(Table.Skip(Excel.Workbook([Content]){0}[Data],2), [PromoteAllScalars=true]),{"Date", "Count", "Header", "Value"})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Date", "Count", "Header", "Value"}, {"Date", "Count", "Header", "Value"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Date", type date}, {"Count", Int64.Type}, {"Header", type text}, {"Value", Currency.Type}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Date] <> null)), #"Replaced Errors" = Table.ReplaceErrorValues(#"Filtered Rows1", {{"Count", 0},{"Value", null}}) in #"Replaced Errors"