Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
M-Ardaneh
Regular Visitor

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 asked MS Copilot to help me explain the code

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]
}),
//This step sorts the table Source by the column A in ascending order first and then by column B in ascending order.
#"Sorted Rows M" = Table.Sort(Source, {{"A", Order.Ascending}, {"B", Order.Ascending}}),
//1. This groups the sorted table by column A.
//2.For each group, it adds an index column called Index starting from 1 and incrementing by 1.
#"Grouped Rows M" = Table.Group(#"Sorted Rows M", {"A"}, {{"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type) }}),

 

//This Column still exist in AllData Table

#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows M",{"A"}),


//1.This expands the grouped tables back into a flat structure.
//2.The expanded table includes the columns A, B, and Index, but renames them to A, B, and Index. (I Didn't rename)
#"Expanded AllData" = Table.ExpandTableColumn(#"Removed Columns", "AllData", {"A", "B", "Index"}, {"A", "B", "Index"})
//This outputs the final expanded table which includes the original data along with the newly added Index column within each group of A.
in
#"Expanded AllData"
//This process ensures each group defined by column A has a sequential index within it, making it easier to identify the order of entries within each group.
First TableFirst TableScreenshot (24).png

1 ACCEPTED SOLUTION
Omid_Motamedise
Super User
Super User

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

 

Omid_Motamedise_0-1732354779580.png

 

 

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])

 

Omid_Motamedise_1-1732354994514.png

 

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"

View solution in original post

4 REPLIES 4
PwerQueryKees
Super User
Super User

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"

 

PwerQueryKees
Super User
Super User

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"

 

PwerQueryKees_0-1732365118178.png

 

This could be made fance, by dynamically setting the various column names.... 

Omid_Motamedise
Super User
Super User

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

 

Omid_Motamedise_0-1732354779580.png

 

 

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])

 

Omid_Motamedise_1-1732354994514.png

 

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"

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors