Forum Discussion

thassan77's avatar
thassan77
Frequent Visitor
5 years ago
Solved

Append multiple sheet in the same excel File

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. ...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    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.

lbendlin's avatar
lbendlin
Super User
5 years ago

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.