Forum Discussion
Solved
Power Query not recognizing header row in some of the multiple excel file from SharePoint folder
Sample Files Hi there, I have multiple users uploading excel files, that follow the same template, onto a Sharepoint folder. Each of the uploaded file is following the exact same template with the...
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.
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"
Apologies for my sample data being unclear.
#"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"})),This portion of the code is assuming the data is in the 3rd row 3rd column at all time. The problem is Power Query reads some of the file as if the headers are starting in cell A1 and some in cell D3 and some in Cell A3.
I updated my sample files accordingly. Could you assist with how the Mcode should be given the sample files now?
Thank you so much!
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.