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 header on the third row and starting on column 3. 

 

I am trying to combine the data within all the files in that sharepoint folder but am having some issue with power query not recognizing the third row as the header.

 

Within PowerBI Power Query, I have a column of the list of tables within each excel. As I am clicking throught the tables, I noticed that the query recognize the headers in only some of the files but not all of the files. In other words, when looking at each file table data, some of the files have "Column 1, column 2, etc" as the header whereas most of the files have the actual header name (3rd row) already as the header in power query tables. 

 

 

Can someone tell me how I can have power query recognize the header for all the files? 

 

I can't insert a step in the query to promote the 3rd row as the header because most of the files already have the 3rd row as the header. So if I were to insert a step to manually have the 3rd row get promoted to header, this will impact the files that already has the 3rd row as the header.

 

The next step in my query was to expand the data column, consisted of the table, to grab all the data with the header "Date, Count, Header, Value". But with some of the tables missing these names in the header, the expansion will exclude any files missing these headers. 

 

Please help!

 

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.

7 Replies