Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table "School" :
School | ||
Class | Names | Counts |
c1 | XXX | 1 |
c1 | YYY | 2 |
c2 | ZZZ | 3 |
c2 | AAA | 4 |
c3 | AAA | 2 |
c3 | BBB | 3 |
c4 | BBB | 2 |
c5 | CCC | 0 |
c5 | CCC | 1 |
c5 | DDD | 2 |
c6 | FFF | 1 |
I have created a 'Total Counts' groupby 'Names' column & created Rank based on 'Total count' Measure.
Names | Total counts | Rank |
AAA | 6 | 1 |
BBB | 5 | 2 |
ZZZ | 3 | 3 |
DDD | 2 | 4 |
YYY | 2 | 4 |
CCC | 1 | 5 |
FFF | 1 | 5 |
XXX | 1 | 5 |
I wanted to create a 'new column' called 'Updated Names' which concatenate the value as shown in the below table based on 'Total Counts' column (Dynamically Grouping by a Measure column - combining the values)
Expected output | ||
Updated Names | Total counts | Rank |
AAA | 6 | 1 |
BBB | 5 | 2 |
ZZZ | 3 | 3 |
DDD,YYY | 2 | 4 |
CCC,FFF,XXX | 1 | 5 |
Solved! Go to Solution.
Hi @Anonymous ,
When you complete this step, write the following statement in the advanced editor, then sort in descending order and add an index column. The index column is your Rank column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XctLCsAgDEXRvWTsoH7auVFcg4k46v730AeKgU4uj0MyBr2eHPXeUU/TbRARNCwImKqKRoOcM5oWxAPBgJntJR3YFzdmKQW9/uANaq328mC21tbF/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Names = _t, Counts = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Names", type text}, {"Counts", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Names"}, {{"Total counts", each List.Sum([Counts]), type nullable number}}),
#"Combined Values"= Table.Group(#"Grouped Rows" , {"Total counts"}, {{"Combine Values", each Text.Combine([Names], ","), type text}}),
#"Sorted Rows" = Table.Sort(#"Combined Values",{{"Total counts", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Rank"}})
in
#"Renamed Columns"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
When you complete this step, write the following statement in the advanced editor, then sort in descending order and add an index column. The index column is your Rank column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XctLCsAgDEXRvWTsoH7auVFcg4k46v730AeKgU4uj0MyBr2eHPXeUU/TbRARNCwImKqKRoOcM5oWxAPBgJntJR3YFzdmKQW9/uANaq328mC21tbF/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Names = _t, Counts = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Names", type text}, {"Counts", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Names"}, {{"Total counts", each List.Sum([Counts]), type nullable number}}),
#"Combined Values"= Table.Group(#"Grouped Rows" , {"Total counts"}, {{"Combine Values", each Text.Combine([Names], ","), type text}}),
#"Sorted Rows" = Table.Sort(#"Combined Values",{{"Total counts", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Rank"}})
in
#"Renamed Columns"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous Well, maybe a measure like:
Measure =
VAR __Rank = [Rank]
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table',[Names],"Counts",SUM('Table'[Counts])),
"Rank",[Rank]
)
RETURN
CONCATENATEX(FILTER(__Table,[Rank]=__Rank),[Names],",")
Check out the July 2025 Power BI update to learn about new features.