Forum Discussion
Adding index based on another Column value (Add Sequential Index to Grouped Data)
in such situation, adding an index column and filtering the rows with the index column less than the index value of on that rows and then filtering the text over extracted rows could be usefull.
consider this table
after adding index column using the below formula can be filtered all the previous rows with the value equal to "A" on the first column.
Table.SelectRows(#"Added Index", (X)=> x[Index]<=_[Index] and x[A]=_[A])
instead of showing them, we can use Table.RowCoun and solve the problem as you whanted. so the whole solution provided as below
let Source = Table.FromRecords({ [A = "A", B = 2], [A = "B", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "5", B = 10], [A = "5", B = 10], [A = "05", B = 10], [A = "05", B = 10], [A = "05", B = 10], [A = "B", B = 10], [A = "B", B = 10], [A = "5", B = 10] }), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Table.RowCount(Table.SelectRows(#"Added Index", (x)=> x[Index]<=_[Index] and x[A]=_[A]))) in #"Added Custom"
I am not at my laptop. I would solve this with a group by and create the index colum in the group by expression. The expand the group by an you are done. Maybe later today I will try it for you.
Here it is
let //This step creates a table from records where each record is a pair of values (A and B). Each pair represents a row in the table. Source = Table.FromRecords({ [A = "A", B = 2], [A = "B", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "A", B = 10], [A = "5", B = 10], [A = "5", B = 10], [A = "05", B = 10], [A = "05", B = 10], [A = "05", B = 10], [A = "B", B = 10], [A = "B", B = 10], [A = "5", B = 10] }), #"Grouped Rows" = Table.Group(Source, {"A"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"B", "Index"}, {"B", "Index"}) in #"Expanded Count"This could be made fance, by dynamically setting the various column names....