Forum Discussion
Getting Rank Order by Group, then ungroup
In Power Query, I am trying to rank order "sales" by customer ID. I grouped by Customer ID using the "All Rows" Operation into "AllData":
=Table.Group(#"Remove old records", {"CustomerId"}, {{"AllData", each _, type table [SaleID=nullable text, SaleDateTime=nullable datetime, CustomerId=nullable text]}})
I then added a custom column for rank order using Table.AddRankColumn based on the sales date named "SalesRank". It works.
=Table.AddColumn(#"Group by CustomerID", "Custom", each Table.AddRankColumn([AllData],
"SaleRank",
{"SaleDateTime", Order.Ascending},
[RankKind = RankKing.Ordinal]
))
However, all the columns are nested in the "Tables", and are duplicated from AllData into "Custom" where my "SalesRank". How should I go about "ungrouping"? I am hoping to wind up with my original table and only the new column "SalesRank" added.
| CustomerID | AllData | Custom |
| 1 | Table | Table |
| 2 | Table | TableTable |
| 3 | Table |
Hi pbi_throw_away,
You can use Projection (or select columns) to only keep the Custom column with additional logic
Projection will look like this:
Table.AddColumn(#"Group by CustomerID", "Custom", each Table.AddRankColumn([AllData], "SaleRank", {"SaleDateTime", Order.Ascending}, [RankKind = RankKing.Ordinal] ))[[Custom]]After the closing parenthesis for the Table.AddColumn function, in a set of square brackets (for projection) you select a column [Custom] (that also requires square brackets). Alternatively using the ribbon you can "Select Columns" OR "Remove other columns" as well, of course.
Finally use the expand column option (sideward arrows in the column header) to bring back all fields including the newly added rank from Custom.
Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!
1 Reply
Hi pbi_throw_away,
You can use Projection (or select columns) to only keep the Custom column with additional logic
Projection will look like this:
Table.AddColumn(#"Group by CustomerID", "Custom", each Table.AddRankColumn([AllData], "SaleRank", {"SaleDateTime", Order.Ascending}, [RankKind = RankKing.Ordinal] ))[[Custom]]After the closing parenthesis for the Table.AddColumn function, in a set of square brackets (for projection) you select a column [Custom] (that also requires square brackets). Alternatively using the ribbon you can "Select Columns" OR "Remove other columns" as well, of course.
Finally use the expand column option (sideward arrows in the column header) to bring back all fields including the newly added rank from Custom.
Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!