Advanced Group By in Power Query: Learn to Rank Like a Pro

How to rank in #PowerQuery the top 4 FIFA WC champions in descending order of number of times the teams have won the world cup along with their years of winning? In this video I show first a 3 step solution with 2 more complex elements and explain these portions, and then I show an alternative step by step approach with UI support (Table.Group). Which one is better suited for you?

#Excel #PowerBI
Like 👍, comment 💬, and share with fellow data enthusiasts to spread the knowledge!

Ready to become a Power Query master? Join me, Matthias Friedmann, and learn advanced data techniques and solve complex Power Query challenges with ease:

[MatthiasFriedmann]

Here are the two alternative approaches. Which one is better suited for you?
1. 3 Steps, but relatively complex:
let
Source = Excel.CurrentWorkbook(){[Name = "WorldCupWins"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Champion"},
{
{"Times of Winning", each Table.RowCount(_), Int64.Type},
{"Years of Winning", each Text.Combine(List.Transform([Year], each Text.From(_)), ", ")}
}
),
#"Filtered Rows" = Table.SelectRows(
#"Grouped Rows",
each [Times of Winning]
>= Rows"[Times of Winning]), Order.Descending){3}
),
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Times of Winning", Order.Descending}})
in
#"Sorted Rows"

2. Step by step with UI support:
let
Source = Excel.CurrentWorkbook(){[Name = "WCchampions"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Year", type text}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Champion"},
{{"Times of Winning", each Table.RowCount(_), Int64.Type}, {"Years of Winning", each [Year]}}
),
#"Grouped Rows1" = Table.Group(
#"Grouped Rows",
{"Times of Winning"},
{
{
"All",
each _,
type table [Champion = text, Times of Winning = number, Years of Winning = list]
}
}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows1", {{"Times of Winning", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 5)[[All]],
#"Expanded All" = Table.ExpandTableColumn(
#"Filtered Rows",
"All",
{"Champion", "Times of Winning", "Years of Winning"},
{"Champion", "Times of Winning", "Years of Winning"}
),
#"Extracted Values" = Table.TransformColumns(
#"Expanded All",
{"Years of Winning", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
)
in
#"Extracted Values"

[qasimjan]

Thank you Matthias for another amazing solution.