Append tables from different workbooks in Power Query while keeping source names in the query result-CodePudding

I'm using the following code to append tables from different workbooks in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workbook location", type text}, {"Workbook name", type text}, {"Table name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(File.Contents([Workbook location]&[Workbook name])){[Item=[Table name],Kind="Table"]}[Data]),
    #"Combine" = Table.Combine(#"Added Custom"[Custom])
in
    #"Combine"

It works - the tables are appended and any new column is included to the query result after refreshing. But I also need to keep the column [Workbook name] in the query result, so that the source file for each record can be identified.

I noticed that using 'Table.ExpandTableColumn' would do this job, but the drawback is that it won't include new columns to the results.

This is the desired result:

CodePudding user response：

How about

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook(File.Contents([Workbook location]&[Workbook name]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Name] = [Table name])),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Workbook name", "Data"}),
#"PromoteHeaders" = Table.TransformColumns(#"Removed Other Columns",{{"Data", each  Table.PromoteHeaders(_, [PromoteAllScalars=true]) , type table}}),
List = List.Union(List.Transform(PromoteHeaders[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"PromoteHeaders", "Data", List,List)
in  #"Expanded Data"

CodePudding user response：

Ok, I got this right by storing the column names from the combined tables using Table.ColumnNames, and then passing this to Table.ExpandTableColumn. Now it will append the tables while addind/removing any new columns and keeping the workbook names.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workbook location", type text}, {"Workbook name", type text}, {"Table name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Table_list", each Excel.Workbook(File.Contents([Workbook location]&[Workbook name]) ){[Item=[Table name],Kind="Table"]}[Data]),
    #"Colnames" = Table.ColumnNames(Table.Combine(#"Added Custom"[Table_list])),
    #"Expanded" = Table.ExpandTableColumn (#"Added Custom","Table_list", #"Colnames")
in
    #"Expanded"