PQ - Summary from template in single workbook

[sandy666]

Assumption:

sheet destination for summary
existing template of the table (here: Table1)
template ID (whatever) contain formula: =RIGHT(CELL("filename",A$1),LEN(CELL("filename",A$1))-SEARCH("]",CELL("filename",A$1)))

https://i.postimg.cc/7LWw5Fmk/start.png

steps:

copy template sheet to the end of the sheets

https://i.postimg.cc/Kz3FB0Hg/copy2theend.png


rename tab of copied template to required name

https://i.postimg.cc/c4SZtMMr/tabs.png


fill template table on this new sheet

https://i.postimg.cc/vHtyrg3X/filltemplate.png


first time Load Query to the Summary tab

https://i.postimg.cc/CKzVpvY6/tableonsummary.png


do the same for the next tabs then just Refresh summary table


// Summary - name of query

let
Source = Excel.CurrentWorkbook(),
FilterTemplate = Table.SelectRows(Source, each ([Name] <> "Summary" and [Name] <> "Table1")),
TSC = Table.SelectColumns(FilterTemplate,{"Content"}),
Expand = Table.ExpandTableColumn(TSC, "Content", {"ID", "Name", "Company", "Country", "City", "Sales"}, {"ID", "Name", "Company", "Country", "City", "Sales"}),
FilterNull = Table.Distinct(Table.SelectRows(Expand, each ([ID] <> null))),
Type = Table.TransformColumnTypes(FilterNull,{{"ID", type text}, {"Name", type text}, {"Company", type text}, {"Country", type text}, {"City", type text}, {"Sales", Int64.Type}})
in
Type

Note: file must be saved before use

Example: 3329