Forum Discussion

txpham16's avatar
txpham16
Frequent Visitor
2 years ago
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...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

txpham16's avatar
txpham16
Frequent Visitor
2 years ago

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!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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.