Forum Discussion
Numbering specific rows based on two columns
I am trying to use dax or a measure to do the following:
Using information that is similar to below I am trying to create a third column that numbers the rows based on the two columns.
ORDER ORDER PART
P0001 1
P0001 1
P0001 2
P0001 2
P0002 1
P0002 2
What I am looking to do:
ORDER ORDER PART LABEL
P0001 1 1
P0001 1 2
P0001 2 1
P0001 2 2
P0002 1 1
P0002 2 1
So if there are two P0001 that have ORDER PART "1" I want to number those rows from 1 to 2 so on and so forth. I am not trying to COUNT them. Thank you ahead of time for any help! I have been struggling on this one.
Hi Evan_Power_Bi ，
You need to use add index by group in power querey，refer:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMDBU0lEyVIrVwcUzwsEzQlFpBJGLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ORDER = _t, #"ORDER PART" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORDER", type text}, {"ORDER PART", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ORDER", "ORDER PART"}, {{"allrows", each _, type table [ORDER=nullable text, ORDER PART=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"Index",1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ORDER", "ORDER PART", "Index"}, {"Custom.ORDER", "Custom.ORDER PART", "Custom.Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"allrows", "Custom.ORDER", "Custom.ORDER PART"}) in #"Removed Columns"Best Regards
Lucien
3 Replies
Hi Evan_Power_Bi ，
You need to use add index by group in power querey，refer:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMDBU0lEyVIrVwcUzwsEzQlFpBJGLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ORDER = _t, #"ORDER PART" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORDER", type text}, {"ORDER PART", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ORDER", "ORDER PART"}, {{"allrows", each _, type table [ORDER=nullable text, ORDER PART=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"Index",1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ORDER", "ORDER PART", "Index"}, {"Custom.ORDER", "Custom.ORDER PART", "Custom.Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"allrows", "Custom.ORDER", "Custom.ORDER PART"}) in #"Removed Columns"Best Regards
Lucien
I think this is the solution I am looking for but I am not the most knowledgeable in the Custom Column in the Power Query. Can you provide some learning tools to use? I will try to input what you have given.
Hi Evan_Power_Bi ,
Do operations according the below links:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
Best Regards
Lucien
