Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 Table
Solved! Go to Solution.
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"
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"
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....
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"
User | Count |
---|---|
20 | |
10 | |
10 | |
9 | |
7 |