Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbi_throw_away
Frequent Visitor

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.

CustomerIDAllDataCustom
1TableTable
2TableTableTable
3Table 

 

power_query.PNG

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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!

 

View solution in original post

1 REPLY 1
m_dekorte
Super User
Super User

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors