Forum Discussion

M-Ardaneh's avatar
M-Ardaneh
Regular Visitor
1 year ago
Solved

Adding index based on another Column value (Add Sequential Index to Grouped Data)

I had a problem with creating an index to start again every time my value from another column changes , something like this excel formula "=COUNTIF($J$2:J40,J40)". Anyway this code worked for me. I ...
Show and Tell
Tips and Tricks
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    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"
PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

Here it is, just for the fun of it:

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]
    }),
    group_colum_names = {"A"},
    ungroup_column_names = List.Combine({List.Difference(Table.ColumnNames(Source),group_colum_names),{"Index"}}),
    #"Grouped Rows" = Table.Group(Source, group_colum_names, {{"__Grouped Index__", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "__Grouped Index__", ungroup_column_names, ungroup_column_names)
in
    #"Expanded Count"