List.Accumulate for list of tables

Power Query
1

Please see attached:

I’m expanding a list of tables. I only want to expand the table if it contains the correct columns. I’m trying to make this dynamic because Ill be grouping lots of different tables by name. Below is process:

  1. get all files from a folder
  2. Inspect each file for specific table names eg. begins with “Company_tbl”
  3. Keep or add table to list if true.
  4. Append all tables in the list which meet conditions 2 and 3 above.

How can I iterate over tables using List.Accumulate and keep based on condition?

Here is my current transforms. Note the List.Accumulate function in Company Transform is broken

Company Transform

let
Source = Folder.Files(f_directory),
headers = {"PersonNames", "People", "Location"},
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
Filter_hidden_files = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
transform = Table.AddColumn(
Filter_hidden_files, "Transform File", 
each t_transform([Content], "Company_tbl01", {}, [Name], [Folder Path])),
expand = Table.ExpandTableColumn(transform, "Transform File", {"Data", "path"}, {"Data", "path"}),
// filter_tables = Table.SelectRows(expand, each [Data]))
_empty_tbl = #table({"PersonNames","People","Location"}, {}),
// BROKEN: List.Accumulate function doesn't work
tbl_filter = List.Accumulate(
[Data], _empty_tbl, (state, current) => 
if Col_check(state, headers)
then Table.Combine(state,current)
else state)
in
tbl_filter

t_transform

let transform_file = (
    bContent as binary, 
    tbltitle as text, 
    headers as list, 
    filename as text, 
    folderpath as text
    ) => 
    let
        headers = null, // TODO dynamic headers list e.g. headers = {"PersonNames", "People", "Location"}. Use this to check through tables
        filepath = folderpath&filename,
        Source = Excel.Workbook(bContent, headers, true),
        Title = Source{[Item=tbltitle,Kind="Table"]}[Data],
        Add_path = Table.AddColumn(Source, "path", each filepath as text)
    in
        Add_path
in
    transform_file

Any

Column checker to check if table contains headers

let 
// checks table columns against given headers (list of col names)
Column_check_dyno = (tbl as table, headers as list) =>
let
col_names = Table.ColumnNames(tbl),
keep_cols = List.Select(col_names,each List.Contains(col_names, headers)), 
select_headersonly = Table.SelectColumns(tbl, keep_cols)
in
select_headersonly
in
Column_check_dyno
2

Hi @izzleee,

Interesting scenario.
I’m away now but love to have a go at this when I get back, if still unresolved.

All the best

3

Hi @izzleee,

Is this what you were looking for?
Paste the code below into a new blank query, in your PBIX file.

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

I hope this is helpful

1 Like
4

Amazing, thanks Melissa!

I only recently noticed the powerquery courses. I’ll jump onto those in the coming weeks.

1 Like
5

Interestingly, because the tables were’nt defined in Sharepoint, I had to add an extra query after GetData step to promote headers.

Table.TransformColumns(GetData, {"Data", Table.PromoteHeaders})

1 Like