Power Querying numerous tables to combine all information into 1 table, then combine duplicate text and add up corresponding counts together

Question

I am new to power query so please excuse my lack of knowledge if the answer is relatively straight forward.

The table below is my initial testing with transforming data from tables into my workbook into one table. This power query will have much more information included when everything is added.

Currently the second photo is how the filters are inventoried, it is however all done manually. I am looking to use power query to query all the main tables for the in use Belt and Filter information to automate so when information is added, changed, or removed from the main tables, it will update the inventory page automatically as well. Columns F1, F2, F3, F4 will have all types of filters sizes in them, so for example 20X24X2-GP shows up in F1 and F3, and may end up showing up in all of them at some point, as any filter size may. I need to combine every instance of each filter size and belt and their corresponding amounts together. So based on the first picture for example, it would be AX30 | 3, 20X24X2-GP | 13 etc.

My current thought is after power querying and appending together every table I could use a formula in the inventory table to read the power queried table to add everything together but it is far beyond my current excel skillset to create such a formula.

All help is greatly appreciated.

On a side note, is there also a way to automate the refreshing of queries when a change is made, or when the document is open or closed, or something of that nature?

Answer 1

Here is a solution. I can't use your table as it is an image so here is a similar starting table:

End result:

Code courtesy of @horseyride

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    //code courtesy of horseyride - start
    base_columns=0, groupsof=2, 
    Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source),base_columns),groupsof), each List.FirstN(Table.ColumnNames(Source),base_columns) & _),
    #"Added Custom" =List.Accumulate(Combo, #table({"Column1"}, {}),(state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1)),
    //code courtesy of horseyride - end
    
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Column1"}, {{"Quantity", each List.Sum([Column2]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Column1] <> null)
in
    #"Filtered Rows"