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

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"

CodePudding user response：

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"