none
Rank & Dens_Rank in Power Query custom column RRS feed

  • Question

  • I want to calculate Rank() and Dens_Rank of one column sample data will be like below

    Company     Rank     Dens_Rank

    Ford               1            1

    Ford               2            1

    BMW              1            2

    Benz              1            3

    Benz              2            3

    Benz              3            3

    Please help me on how can i achieve Rank & Dens_Rank columns data based on Company name column. 


    • Edited by SBS152 Wednesday, April 4, 2018 11:15 AM
    Wednesday, April 4, 2018 11:14 AM

Answers

  • Rank and Dense Rank. Provided table used as an Excel table named "Table1" with just the Company column.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"Company"}, {{"Table", each Table.AddIndexColumn(_, "Rank", 1), type table}}),
        ExpandedTable = Table.ExpandTableColumn(GroupedRows, "Table", {"Rank"}),
        AddedCustom = Table.AddColumn(ExpandedTable, "Dens_Rank", each List.PositionOf(List.Distinct(ExpandedTable[Company]),[Company]) + 1)
    in
        AddedCustom


    • Edited by Colin Banfield Wednesday, April 4, 2018 3:21 PM
    • Marked as answer by SBS152 Thursday, April 5, 2018 10:21 AM
    Wednesday, April 4, 2018 2:15 PM

All replies

  • Regarding the 'Rank' column - it's more row index than rank as such, correct? are there any other columns in that table?

    Regarding Dens_Rank try the following:

    let
        Source = #table({"Company"},{{"Ford"},{"Ford"}, {"BMW"},{"Benz"},{"Benz"},{"Benz"}}),
        Company_Select = Table.SelectColumns(Source,{"Company"}),
        Company_Unique = Table.Distinct(Company_Select),
        DensRank_Index = Table.AddIndexColumn(Company_Unique, "Dens_Rank", 1, 1),
        Source_Merge_DensRank = Table.NestedJoin(Source,{"Company"},DensRank_Index,{"Company"},"Custom",JoinKind.LeftOuter),
        #"Expanded Custom" = Table.ExpandTableColumn(Source_Merge_DensRank, "Custom", {"Dens_Rank"}, {"Dens_Rank"})
    in
        #"Expanded Custom"

    Wednesday, April 4, 2018 1:59 PM
  • Rank and Dense Rank. Provided table used as an Excel table named "Table1" with just the Company column.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"Company"}, {{"Table", each Table.AddIndexColumn(_, "Rank", 1), type table}}),
        ExpandedTable = Table.ExpandTableColumn(GroupedRows, "Table", {"Rank"}),
        AddedCustom = Table.AddColumn(ExpandedTable, "Dens_Rank", each List.PositionOf(List.Distinct(ExpandedTable[Company]),[Company]) + 1)
    in
        AddedCustom


    • Edited by Colin Banfield Wednesday, April 4, 2018 3:21 PM
    • Marked as answer by SBS152 Thursday, April 5, 2018 10:21 AM
    Wednesday, April 4, 2018 2:15 PM