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

AddRankColumn only when a condition exists

By customer ID, I am trying to rank order records but only when columnA = true. If not true/false, then the value can be 0.

 

Is this possible using Table.AddRankColumn?

 

If not, is there a way to rank order records only when the value is true?

 

Thank you

1 REPLY 1
m_dekorte
Super User
Super User

Hi @pbi_throw_away 

 

Give something like this a go, where you group by customer and columnA to conditionally add a rank

Copy this example into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZE7EsIwDETv4jpF9LOkkgIqKj5VJve/BtgGJmYLN2/W8ltr28rj9jyXpdT3kdBa9uXHqDFTPzJubGU5MhksgTFNOW3M1/HG5XS9N+gNivIx2GVIpoHRBzJBTilxYFWCy+krGApVaPd3tzNxbKys0E6NJplhnQEDgxQExQ0Y0fxd1l+pk81oTGhtgblkhh1rJDBXzEm1SWY04YrfEIaLjymXvV36NLAH42P9hX3LaxhC07f3/gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, #"Customer ID" = _t, Order = _t]),
    GroupRows = Table.Group(Source, {"ColumnA", "Customer ID"}, {{"t", each if List.First([ColumnA]) = "TRUE" then Table.AddRankColumn( _, "Rank", {"Order", Order.Descending}) else Table.AddColumn( _, "Rank", each 0 ), type table [ColumnA=nullable text, Customer ID=nullable text, Order=nullable text, Rank=number]}}),
    AddCustOrderRank = Table.ExpandTableColumn(GroupRows, "t", {"Order", "Rank"}, {"Order", "Customer Order Rank"})
in
    AddCustOrderRank

 

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