PQ - Count sub-levels in Pivot Table

[sandy666]

https://i.postimg.cc/SxxTC3vT/countsublevels.png


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Table.SelectRows(Source, each ([Members] <> null)), {"Teams"}, {{"Car", each _, type table}, {"Count", each Table.RowCount(_), type number}}),
Brackets = Table.TransformColumns(Table.TransformColumns(Grou p, {{"Count", each "(" & Text.From(_, "en-GB"), type text}}), {{"Count", each _ & ")", type text}}),
Join = Table.CombineColumns(Brackets,{"Teams", "Count"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Count"),
TSC = Table.SelectColumns(Table.ExpandTableColumn(Join, "Car", {"Members"}, {"Members"}),{"Count", "Members"})
in
TSC
after M go to tab INSERT - Pivot Table, select use an external data source then select Query table, Open, choose the place for Pivot Table then OK

source data:

TeamsMembers

Team BlueFelix Weiss

Team BlueMaggy Z. Cruz

Team BlueMelyssa Hale

Team BlueBeau E. Colon

Team BlueBrenden Roach

Team BlueAhmed U. Joyner

Team BlueSigne Reilly

Team BlueCandice G. Soto

Team BlueCasey N. Rivas

Team BlueBevis Whitney

Team BlueRalph D. Andrews

Team BlueCherokee Anthony

Team BlueZachary Stout

Team BlueOra Hill

Team BlueBuffy B. Rosa

Team RedBlair Cooley

Team RedDamian E. Saunders

Team RedDaphne Z. Nguyen

Team RedRina Chaney

Team RedIvor Dudley

Team RedNolan E. Park

Team RedChastity Lewis

Team RedOlivia K. Humphrey

Team RedAhmed F. Sherman

Team RedOscar Franklin

Team RedNoah Flowers

Team RedQuemby Bond

Team RedKadeem O. Colon

Team RedReuben O. Pacheco

Team RedSebastian B. Wynn

Team RedDenton K. Cooke

Team RedBernard E. Barrett

Team RedVictoria N. Pace

Team RedJesse Edwards

Team RedNita T. Lawrence

Team RedGalvin Bird

Team RedAllen Bright

Team RedBo Sweeney

Team RedCasey Odom

Team RedWhitney Vargas

Team RedLaurel Ayala

Team RedFrancesca Huff

Team RedYoshi Mills

Team RedFinn Fitzpatrick

Team RedZelda Weiss

Team RedKeith Osborn

Team RedNell T. Cash

Team RedHolly M. Gould

Team RedSavannah W. Rosario

Team RedThomas Salas

Team RedJoan Steele

Team RedHillary Y. Mann

Team RedMelissa H. Ellis

Team RedBert Bradford

Team PinkHiram Myers

Team PinkMohammad Mcconnell

Team PinkTatiana Gallegos

Team PinkMaxwell Y. Galloway

Team PinkBlythe Schneider

Team PinkDebra Patton

Team PinkWyoming D. Berg

Team PinkSade Dillard

Team PinkAbraham Z. Pate

Team PinkRenee Grant

Team PinkKeegan K. Kelly

Team PinkKessie N. Hutchinson

Team PinkAllistair Foley

Team PinkBo Knight

Team PinkClaudia V. Grant

Team PinkShellie U. Joyce

Team PinkRhiannon V. Hardin

Team PinkTanek T. Conley

Team PinkReed J. Dunlap

Team PinkCody Molina

Team PinkCaleb J. Powers

Team PinkAlea Mayo

Team PinkIona D. Knight

Team PinkOleg X. Farrell

Team GreenClaire N. Wyatt

Team GreenAlan H. Good

Team GreenOrson Gould

Team GreenYoshi Vincent

Team GreenCiaran Brady

Team GreenTimothy Scott

Team GreenPortia Y. Franco

Team GreenMarvin W. Norman

Team BlackOwen K. Charles

Team BlackGarrison X. Burke

Team BlackTasha Bridges

Team BlackFallon Q. Ferguson

Team BlackJermaine Cochran

Team BlackYolanda Hernandez

Team BlackBradley B. Olson

Team BlackGarrison Q. Clements

Team BlackMaxine D. Talley

Team BlackColt Galloway

Team BlackLawrence Robertson

Team BlackRashad Witt

Team BlackHarding B. Leonard

Team BlackChanda Dudley