Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |