Getting Rank Order by Group, then ungroup

[pbi_throw_away]

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

[m_dekorte · Accepted solution]

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!