Multiple Ranking Columns in Power Query

[slackerhx]

Hi All, 

I'm trying to search for a solution to create multiple ranking columns in Power Query. 

Previously, i was using Excel as data source and manage to create multiple ranking columns for the different attributes using Excel's RANK function. After which, i can unpivot the column names in Power Query to form Attributes and Rank columns. Please see data sample below: 

EXCEL:

City	GDP Per Capita	Rank by GDP	Unemployment Rate	Rank by Unemployment Rate	Air Passenger Volume	Rank by Air Passenger Volume
Sydney	67001	1	4.60%	2	46216607	3
Taipei	44173	2	3.90%	3	53507961	2
Beijing	17811	3	1.24%	4	101878600	1
Manila	15615	4	6.38%	1	43100815	4

Power Query: 

City	Attributes	Rank
Sydney	Rank by GDP	1
Sydney	Rank by Unemployment Rate	2
Sydney	Rank by Air Passenger Volume	3
Taipei	Rank by GDP	2
Taipei	Rank by Unemployment Rate	3
Taipei	Rank by Air Passenger Volume	2
Beijing	Rank by GDP	3
Beijing	Rank by Unemployment Rate	4
Beijing	Rank by Air Passenger Volume	1
Manila	Rank by GDP	4
Manila	Rank by Unemployment Rate	1
Manila	Rank by Air Passenger Volume	4

However, due to company policy, i have to change my source to Sharepoint List which makes it necessary for me to re-create the Ranking Columns in PowerBI instead.

I have tried creating the ranking using DAX, however, if the ranking was done through DAX, i'm not sure how i can unpivot the different Ranking Field Names to rows under an Attribute Column as shown above. I will need to have "Attribute" and "Rank" columns for use in the Radar Chart visual. 

I would greatly appreciate if anyone can enlighten me using DAX or Power Query to achieve the above Attribute and Rank columns and it is also necessary to have ties in the Ranks. 

Thank you. 

[v-juanli-msft · Accepted solution]

Hi @slackerhx

This formula will help with DAX

Table =
VAR table1 =
    SUMMARIZE ( Sheet1, [City], [Rank by GDP], "Attributes", "Rank by GDP" )
VAR table2 =
    SUMMARIZE (
        Sheet1,
        [City],
        [Rank by Unemployment Rate],
        "Attributes", "Rank by Unemployment Rate"
    )
VAR table3 =
    SUMMARIZE (
        Sheet1,
        [City],
        [Rank by Air Passenger Volume],
        "Attributes", "Rank by Air Passenger Volume"
    )
RETURN
    UNION ( table1, table2, table3 )

Best Regards

Maggie

[slackerhx]

thanks Maggie ! @v-juanli-msft