Add column to each table in list of tables

Power Query
1

This question follows on from @Melissa’s solution to this problem: combining list of tables

See attached files:

Company_1.xlsx (16.2 KB)
Company_2.xlsx (16.3 KB)
Table_list_addcols.pbix (34.9 KB)

How can I use Table.AddColumn to dynamically add filename column for each table in column GetData[Data] (list of tables)?

See below transform which doesn’t work :frowning: I’ve commented my failed attempt called tbl_add_filename. I also started looking at List.Zip but suspect zipping makes it overly complicated.

let
Headers = {"PeopleNames", "People", "Location"},
Source = Folder.Files(f_directory),
tbl_from_Content = Table.AddColumn(Source, "table", each Excel.Workbook([Content])),
xlsx_only = Table.SelectRows(tbl_from_Content, each ([Extension] = ".xlsx")),
GetData = Table.ExpandTableColumn(xlsx_only, "table", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name2", "Data", "Table_name", "Kind", "Hidden"}),

// tbl_add_filename = Table.TransformColumns(GetData, {"Data", each Table.AddColumn(_, "subby", GetData[Name])}), //How to grab column from GetData table and put into [Data]

ToTable = Table.Combine( 
List.Transform( 
Table.SelectRows(GetData, each Text.StartsWith([Table_name], "Company_tbl"))[Data], 
each Table.SelectColumns( _, Headers )))
in
ToTable
2

Hi @izzleee,

Give this a go instead.

let
    Headers = {"PeopleNames", "People", "Location"},
    Source = Folder.Files(f_directory),
    AddContent = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
    GetData = Table.SelectRows( Table.ExpandTableColumn(AddContent, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name2", "Data", "Item", "Kind", "Hidden"}), each Text.StartsWith([Item], "Company_tbl"))[[Name], [Data]],
    ExpandData = Table.ExpandTableColumn( GetData, "Data", Headers, Headers)
in
    ExpandData

With this result.

image

I hope this is helpful.

1 Like
3

Thanks @Melissa. Really appreciate this!

4

Glad I could help @izzleee :+1: