Combining multiple sheets with different columns using Power Query

Question

I have a workbook with multiple pages that need to get combined, i.e. stacked, into one table. While they have many similar column names, they do not all have the same columns and the column order differs. Because of this I cannot use the inherent merge functionality because it uses column order. Table.Combine will solve the problem, but I cannot figure out to create a statement that will use the "each" mechanic to do that.

For each worksheet in x workbook Table.Combine(prior sheet, next sheet) return all sheets stacked.

Would someone please help?

Answer 1

• Load each table into Power Query as a separate query
• fix up the column names as needed for each individual query
• save each query as a connection
• in one of the queries (or in a separate query) use the Append command to append all the fixed up queries that now have the same column names.

Answer 2

If you load your workbook with Excel.Workbook you can choose the Sheet Kind (instead of Table or DefinedName kinds) and ignore the sheet names.

let
    Source = Excel.Workbook(File.Contents("C:\Path\To\File\FileName.xlsx"), null, true),
    #"Filter Sheets" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Promote Headers" = Table.TransformColumns(#"Filter Sheets", {{"Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}}),
    #"Combine Sheets" = Table.Combine(#"Promote Headers"[Data])
in
    #"Combine Sheets"