Need help joining X# tables from files with dynamic columns using FilingID column

Hi jrrs 

 Try this

let
    Source = SharePoint.Files("https://_.sharepoint.com", [ApiVersion = 15]),
    FilteredRows = Table.SelectRows(Source, each [Folder Path] = "https://_.sharepoint.com/sites/dev/ODC/MayRaw/Filing_Data_20250501_202505311/"),
    FilteredHiddenFiles = Table.SelectRows(FilteredRows, each [Attributes]?[Hidden]? <> true),
    InvokedTransform = Table.AddColumn(FilteredHiddenFiles, "Transform File", each #"Transform File"([Content])),
    RenamedColumns = Table.RenameColumns(InvokedTransform, {"Name", "Source.Name"}),
    RemovedOtherColumns = Table.SelectColumns(RenamedColumns, {"Source.Name", "Transform File"}),

    // Extract the list of tables
    TableList = RemovedOtherColumns[Transform File],

    // Merge all tables on FilingID using List.Accumulate
    MergedTable = List.Accumulate(
        TableList,
        TableList{0}, // Start with the first table
        (state, current) => Table.Join(state, "FilingID", current, "FilingID", JoinKind.FullOuter)
    )
in
    MergedTable

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

Hi jrrs 

I've recreated the issue with a list of tables. You can replace these with the end result of your other query but for testing this might be helpful:

let
Table1 = Table.FromRows( //Generate fake data 1
{
{1, "afa", "UK"},
{2, "abc", "da"}
},
{"FilingID", "StuffA", "StuffB"}
),

Table2 = Table.FromRows(//Generate fake data 2
{
{1, "x", "y"},
{3, "z", "w"}
},
{"FilingID", "StuffE", "StuffF"}
),
Table3 = Table.FromRows( //Generate fake data 3
{
{2, "x", "y"},
{1, "z", "w"}
},
{"FilingID", "StuffC", "StuffD"}
),

AllTables = {Table1, Table2, Table3} //Combine fake tables, replace this with the output of your Folder join
,

FirstTable = List.First(AllTables), //freturn the first table everything else will join to
RemainingTables = List.Skip(AllTables, 1), //Ignore the first row of the table
IndexedTables = List.Zip({RemainingTables, List.Numbers(1, List.Count(RemainingTables))}), //Index the column names using a count
CombinedTable = List.Accumulate( //loop over the tables running a function on each
IndexedTables, //see above
FirstTable, //see above
(state, pair) => //two parameters to pass in
let
nextTable = pair{0}, //return name
index = pair{1}, //return the index number
renamedTable = Table.RenameColumns(nextTable, {"FilingID", "FilingID" & Text.From(index)}) //rename the column so no clash when merging (Filing1, Filing2 etc)
in
Table.Join(state, "FilingID", renamedTable, "FilingID" & Text.From(index), JoinKind.FullOuter) //Full outer join so every column frm every table returned
),

//optionally loop through and create a combined filing index column
//if every ID is represented in every file this is no nedded
FilingIDColumns = List.Select(Table.ColumnNames(CombinedTable), each Text.Contains(_, "FilingID")),

// Use the above list of names to add a new column, remove the empty rows
AddCombinedColumn = Table.AddColumn(CombinedTable, "CombinedFilingID", each
List.RemoveNulls(List.Transform(FilingIDColumns, (col) => Record.Field(_, col)))
),

// Take the list of column names and return a single column
FlattenedColumn = Table.TransformColumns(AddCombinedColumn, {"CombinedFilingID", each if List.Count(_) > 0 then _{0} else null})

//Use the previously created list of names to delete the old columns
, DeleteColumns = Table.RemoveColumns(FlattenedColumn, FilingIDColumns),
//reorder to put the CombinedFilingID at the start but first remove the CombinedFilingID column then add it to the start
#"Reordered Columns" = Table.ReorderColumns(DeleteColumns, List.Combine({{"CombinedFilingID"}, List.RemoveItems(Table.ColumnNames( DeleteColumns),{"CombinedFilingID"})}))
in
#"Reordered Columns"