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.
Dears,
I have a table with many columns. One of the Columns is ProjectCBS. For each group of ProjectCBS, I need the numbering as 1,2,3.. as shown below. - RANKX(SelfService_Plan_DailyPlanQuantity,SelfService_Plan_DailyPlanQuantity[ProjectCBS],,1,Dense)
I tried this formula, but it is giving me same number for the group. I wish the output in the format shown below.
Solved! Go to Solution.
@jesly_ajin assuming you have a column to break the tie or add an Index column and use following expression for new column to achieve the result.
RANK =
RANKX (
FILTER (
'Table',
'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
),
'Table'[Index], ,
ASC,
Dense
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @jesly_ajin ,
To get the rank column in power query directly suing the code like that.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjK0NDJRitXBxTK0tCCeFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectCBS = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectCBS", Int64.Type}}),
Partition = Table.Group(Source, {"ProjectCBS"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Partition.Index"})
in
#"Expanded Partition1"
Do you have any column to break the tie for project CBS
@jesly_ajin assuming you have a column to break the tie or add an Index column and use following expression for new column to achieve the result.
RANK =
RANKX (
FILTER (
'Table',
'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
),
'Table'[Index], ,
ASC,
Dense
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jesly_ajin you need to have another column to rank within the group? Do you have any date or other column which can make each row unique if not then add Index column in Power Query and then use the Rank function.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |