Combine Data from Multiple Worksheets with Sheet Names & Different Columns Using Power Query

Question

Dear all master,

I tried with Power Query but the results were not what I wanted and each worksheet I didn't want to create with a table.

I tried the result already wanting to get close to the result I wanted but the lack of sheet name / name does not appear in the output in Power Query. How does the sheet name appear with the power query?.

Thanks

worksheet name : FIRST

worksheet name : SECOND

worksheet name : THIRD

DESIRED RESULT

RESULT IN POWER QUERY

let
    Source = Excel.Workbook(File.Contents("C:\Users\ADMIN2\Documents\TEST.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]),
    #"Filtered Rows" = Table.SelectRows(#"Combine Sheets", each [KODE] <> null and [KODE] <> "")
in
    #"Filtered Rows"

horseyride · Accepted Answer · 2022-12-13 10:42:55Z

1

This combines all tabs from all files in a directory

let Source = Folder.Files("C:\subdirectory\directory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in  #"Expanded Data"

answered Dec 13, 2022 at 10:42

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

roy Over a year ago

Thank you for your reply. Although this uses different sources but the results can achieve what I want

Collectives™ on Stack Overflow

Combine Data from Multiple Worksheets with Sheet Names & Different Columns Using Power Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related