none
Power Query Rank Functionality RRS feed

  • Question

  • domain Rank
    A 1
    A 1
    A 1
    B 2
    B 2
    C 3
    How to generate custom column 'Rank' which ranks on the frequency of domains in power query. Please if some one can help me, I spent more than a week on this. I do not want to use 'Group-By' option.  


    Wednesday, November 26, 2014 4:51 PM

Answers

  • Umair,

    If you send the output of Power Query to "Data Model" a.k.a. Power Pivot, you can accomplish the same there as a calculated column. Download Excel Workbook.

    FrequencyCount=CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Domain] = EARLIER ( Table1[Domain] )
        )
    )
    
    
    Rank=RANKX (
        VALUES ( Table1[FrequencyCount] ),
        Table1[FrequencyCount]
    )
    
    

    Power Pivot Rank Function

    It uses the RANKX function, you may find some helpful articles here


    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response

    Friday, November 28, 2014 6:52 PM
  • The reason for doing the "merge" is to add the other columns back after the "group by". Here's an example:

    let
        Source = Table.FromRecords({
            [Domain="A", Column2=1, Column3=#date(1969, 1, 5)],
            [Domain="A", Column2=5, Column3=#date(1970, 1, 18)],
            [Domain="A", Column2=9, Column3=#date(1972, 4, 23)],
            [Domain="B", Column2=2, Column3=#date(1974, 9, 24)],
            [Domain="B", Column2=6, Column3=#date(2012, 3, 25)],
            [Domain="C", Column2=5, Column3=#date(1928, 5, 20)]}),
        #"Grouped Rows" = Table.Group(Source, {"Domain"}, {{"Count", each Table.RowCount(_), type number}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
        Merged = Table.Join(#"Sorted Rows", "Domain", Source, "Domain")
    in
        Merged

    Note that I did this all in a single query, but that required writing the merge step by hand. If you create a separate query for the ranking, you can use the UI to do the merge.

    Sunday, November 30, 2014 1:05 PM

All replies

  • Hi,

    Why don't you want to use group-by? You could do this and then do a merge over the "domain" column to add the "Rank" column.

    Regards,

    Tristan

    Wednesday, November 26, 2014 7:35 PM
    Moderator
  • Thank you Tristan for the reply.

    I've other columns along with "Domain". When i try to "Group-by" over the domain i could not get the rest of the columns to be loaded in the worksheet. What i want is to add only a custom column "Rank" so that i can get the top N rows based on Ranking. Moreover i've around 6 millions rows of data so it takes much time to load the data into the worksheet that is why i preferred to set up Top N rows mechanism in order not to import all of the data.

    Best Regards.
    Wednesday, November 26, 2014 9:17 PM
  • Umair,

    If you send the output of Power Query to "Data Model" a.k.a. Power Pivot, you can accomplish the same there as a calculated column. Download Excel Workbook.

    FrequencyCount=CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Domain] = EARLIER ( Table1[Domain] )
        )
    )
    
    
    Rank=RANKX (
        VALUES ( Table1[FrequencyCount] ),
        Table1[FrequencyCount]
    )
    
    

    Power Pivot Rank Function

    It uses the RANKX function, you may find some helpful articles here


    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response

    Friday, November 28, 2014 6:52 PM
  • The reason for doing the "merge" is to add the other columns back after the "group by". Here's an example:

    let
        Source = Table.FromRecords({
            [Domain="A", Column2=1, Column3=#date(1969, 1, 5)],
            [Domain="A", Column2=5, Column3=#date(1970, 1, 18)],
            [Domain="A", Column2=9, Column3=#date(1972, 4, 23)],
            [Domain="B", Column2=2, Column3=#date(1974, 9, 24)],
            [Domain="B", Column2=6, Column3=#date(2012, 3, 25)],
            [Domain="C", Column2=5, Column3=#date(1928, 5, 20)]}),
        #"Grouped Rows" = Table.Group(Source, {"Domain"}, {{"Count", each Table.RowCount(_), type number}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
        Merged = Table.Join(#"Sorted Rows", "Domain", Source, "Domain")
    in
        Merged

    Note that I did this all in a single query, but that required writing the merge step by hand. If you create a separate query for the ranking, you can use the UI to do the merge.

    Sunday, November 30, 2014 1:05 PM