Power Query Rank Functionality

• 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

• 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]
)

```

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

Regards, Avi www.powerpivotpro.com

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
• 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]
)

```

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

Regards, Avi www.powerpivotpro.com

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