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)))

steps:

• copy template sheet to the end of the sheets

• rename tab of copied template to required name

• fill template table on this new sheet

• first time Load Query to the Summary tab

• 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: s666-PQ-summary.xlsx