PQ - Count sub-levels in Pivot Table
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 OKCode: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(Group, {{"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
source data:
Teams Members Team Blue Felix Weiss Team Blue Maggy Z. Cruz Team Blue Melyssa Hale Team Blue Beau E. Colon Team Blue Brenden Roach Team Blue Ahmed U. Joyner Team Blue Signe Reilly Team Blue Candice G. Soto Team Blue Casey N. Rivas Team Blue Bevis Whitney Team Blue Ralph D. Andrews Team Blue Cherokee Anthony Team Blue Zachary Stout Team Blue Ora Hill Team Blue Buffy B. Rosa Team Red Blair Cooley Team Red Damian E. Saunders Team Red Daphne Z. Nguyen Team Red Rina Chaney Team Red Ivor Dudley Team Red Nolan E. Park Team Red Chastity Lewis Team Red Olivia K. Humphrey Team Red Ahmed F. Sherman Team Red Oscar Franklin Team Red Noah Flowers Team Red Quemby Bond Team Red Kadeem O. Colon Team Red Reuben O. Pacheco Team Red Sebastian B. Wynn Team Red Denton K. Cooke Team Red Bernard E. Barrett Team Red Victoria N. Pace Team Red Jesse Edwards Team Red Nita T. Lawrence Team Red Galvin Bird Team Red Allen Bright Team Red Bo Sweeney Team Red Casey Odom Team Red Whitney Vargas Team Red Laurel Ayala Team Red Francesca Huff Team Red Yoshi Mills Team Red Finn Fitzpatrick Team Red Zelda Weiss Team Red Keith Osborn Team Red Nell T. Cash Team Red Holly M. Gould Team Red Savannah W. Rosario Team Red Thomas Salas Team Red Joan Steele Team Red Hillary Y. Mann Team Red Melissa H. Ellis Team Red Bert Bradford Team Pink Hiram Myers Team Pink Mohammad Mcconnell Team Pink Tatiana Gallegos Team Pink Maxwell Y. Galloway Team Pink Blythe Schneider Team Pink Debra Patton Team Pink Wyoming D. Berg Team Pink Sade Dillard Team Pink Abraham Z. Pate Team Pink Renee Grant Team Pink Keegan K. Kelly Team Pink Kessie N. Hutchinson Team Pink Allistair Foley Team Pink Bo Knight Team Pink Claudia V. Grant Team Pink Shellie U. Joyce Team Pink Rhiannon V. Hardin Team Pink Tanek T. Conley Team Pink Reed J. Dunlap Team Pink Cody Molina Team Pink Caleb J. Powers Team Pink Alea Mayo Team Pink Iona D. Knight Team Pink Oleg X. Farrell Team Green Claire N. Wyatt Team Green Alan H. Good Team Green Orson Gould Team Green Yoshi Vincent Team Green Ciaran Brady Team Green Timothy Scott Team Green Portia Y. Franco Team Green Marvin W. Norman Team Black Owen K. Charles Team Black Garrison X. Burke Team Black Tasha Bridges Team Black Fallon Q. Ferguson Team Black Jermaine Cochran Team Black Yolanda Hernandez Team Black Bradley B. Olson Team Black Garrison Q. Clements Team Black Maxine D. Talley Team Black Colt Galloway Team Black Lawrence Robertson Team Black Rashad Witt Team Black Harding B. Leonard Team Black Chanda Dudley
Bookmarks