Forum Discussion
Solved
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 OR...
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
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