PQ - Summary from template in single workbook

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

Code:

---

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: Attachment 3329