1

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?

Uziel
  • 349
  • 4
  • 9

2 Answers2

0
  • 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.
teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Thank you for the quick response. The reason that won't work is that the sheet names will change over time. I need a programmatic solution. I know it can be done, just not sure how. :) – Uziel Nov 18 '20 at 20:51
0

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"
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Hi Alexis, this is what I tried, but does not work. The reason it does not work is that it combines the files based on column position, which are the column names here. We need to promote the first row of every sheet to column name before the combine. So, it would take what you did through "Filter Sheets", then promote the first row, then combine that result. – Uziel Nov 18 '20 at 22:38
  • Good point. I've added that promotion as a new step in my code. – Alexis Olson Nov 18 '20 at 22:49
  • Well done sir. I did not think to just promote each header and then combine. Nice! – Uziel Nov 19 '20 at 03:49