Search code examples
excelpowerbipowerquerypowerbi-desktopm

Ranking in a Group using power query

I would like to groupby and rank the sample data set. I was grouping the dataset based on Factor and YearNum before ranking it using = Table.AddRankColumn(#"previousStep","RankbyYear_WithInTheFactorLevel",{"YearNum",Order.Descending}) but seems it is not recognizing the previous step in power query. is there a way to get around this ? many thanks in advance.

the sample data set

   Factor  YearNum  Col1   Col2 ....
    A       2024
    A       2024
    A       2025
    B       2024
    B       2023

Expected Answer

Factor  YearNum  Col1  Col2 .... RankbyYear_WithInTheFactorLevel 
A       2024                          2
A       2024                          2
A       2025                          1
B       2024                          1 
B       2023                          2
Solution

  • enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factor", type text}, {"YearNum", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Factor"}, {{"All", each _, type table [Factor=nullable text, YearNum=nullable number]}}),
    Custom1 = Table.TransformColumns( #"Grouped Rows", {{"All", each  Table.AddRankColumn( _, "rank", {{"YearNum", Order.Descending}} )}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"YearNum", "rank"}, {"YearNum", "rank"})
in
    #"Expanded All"