Append multiple sheet in the same excel File

[thassan77]

Ok guys, help me out. I have one excel file with multiple tab (sheet) in it. All I want to create another excel file using power query and merge the data into one large file in a different location. So in future i can create more tabs/sheet in the source file and the output file will be updated when refresh. Basically all i want get data from a single excel file which has multiple tabs and transform it into one large data into a separate file.

Appreciate your help.

[lbendlin · Accepted solution]

This can be done easily with a slight modification of the Power Query code.

Connect to the Excel file as usual. Pick one sheet, and say "Transform data"

Then remove the second step, and expand all sheets instead.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\test5.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Data"

After that do some cleanup on the sheet headers as needed.