Forum Discussion
Custom column for counting processes with different IDs.
Hi,
I'm new to Power Query with M codes.
I'm trying to finde out how often an ID in my table reaches status 40 to 60 or higher. If a status 40 doesn't continuously reach status 60, the count isn't looped and only starts again with a new status 40 and counting starting new.
See the screenshot for better understanding.
Does anyone have an idea what this might look like in Power Query?
Many thanks in advance
best regards
BlueSub
let fx_seq = (tbl) => [ val = List.Buffer(tbl[Value]), seq_list = List.Zip({val, List.Skip(val, 1), List.Skip(val, 2)}), positions = List.Buffer(List.PositionOf(seq_list, {40, 50, 60}, Occurrence.All)), tbl_to_join = #table( {"desired result", "i"}, List.TransformMany( List.Positions(positions), (x) => List.Numbers(positions{x}, 3), (x, y) => {x + 1, y} ) ), result = Table.Join(Table.AddIndexColumn(tbl, "idx"), "idx", tbl_to_join, "i", JoinKind.LeftOuter), sort = Table.RemoveColumns(Table.Sort(result, "idx"), {"idx", "i"}) ][sort], Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], group = Table.Group(Source, "ID", {"x", fx_seq}), z = Table.Combine(group[x]) in z
9 Replies
Hello BlueSub - Thanks for posting in the Fabric Community. Below is how you can achieve the result, with the assumption that the count is calculated as the number of times the ID, on a given date, is greater than or equal to 40 and less than or equal to 60. Please let me know if you have any questions.
let // Sample data table with ID, Date, and Value columns Source = Table.FromRecords({ [ID = 1, Date = #date(2025, 5, 1), Value = 40], [ID = 1, Date = #date(2025, 5, 1), Value = 50], [ID = 1, Date = #date(2025, 5, 2), Value = 60], [ID = 2, Date = #date(2025, 5, 1), Value = 20], [ID = 2, Date = #date(2025, 5, 1), Value = 30], [ID = 2, Date = #date(2025, 5, 2), Value = 40], [ID = 3, Date = #date(2025, 5, 1), Value = 10], [ID = 3, Date = #date(2025, 5, 1), Value = 20], [ID = 3, Date = #date(2025, 5, 2), Value = 30] }), // Add a custom column that returns the value from one row below AddNextRowValue = Table.AddColumn(Source, "NextRowValue", each try Source[Value]{[ID]-1} otherwise null), // Group by ID and Date, and add a custom column that counts the number of times the ID has a value greater than 20 for a given date GroupedTable = Table.Group(Source, {"ID", "Date"}, { {"AllData", each _, type table [ID=Int64.Type, Date=Date.Type, Value=Int64.Type]}, {"CountGreaterThan20", each List.Count(List.Select([Value], each _ >= 40 and _ <= 60)), Int64.Type} }), // Keep only the aggregated column and the result. #"Removed Other Columns" = Table.SelectColumns(GroupedTable,{"AllData", "CountGreaterThan20"}), // Expand the grouped table to include the original columns and the new custom column ExpandedTable = Table.ExpandTableColumn(#"Removed Other Columns", "AllData", {"ID", "Date", "Value"}), // Sort the results #"Sorted Rows" = Table.Sort(ExpandedTable,{{"ID", Order.Ascending}, {"Date", Order.Ascending}, {"Value", Order.Ascending}}) in #"Sorted Rows"Hello jennratten
Thanks for Your help and Your reply.
Perhaps I have not expressed myself clearly.
The result should then look as simulated in the “desired result” column. I have attached a screenshot with the result from your code and my desired result. Each new loop (status 40 to status 60) with the same ID should be increased by one count in my "Count" column.let fx_seq = (tbl) => [ val = List.Buffer(tbl[Value]), seq_list = List.Zip({val, List.Skip(val, 1), List.Skip(val, 2)}), positions = List.Buffer(List.PositionOf(seq_list, {40, 50, 60}, Occurrence.All)), tbl_to_join = #table( {"desired result", "i"}, List.TransformMany( List.Positions(positions), (x) => List.Numbers(positions{x}, 3), (x, y) => {x + 1, y} ) ), result = Table.Join(Table.AddIndexColumn(tbl, "idx"), "idx", tbl_to_join, "i", JoinKind.LeftOuter), sort = Table.RemoveColumns(Table.Sort(result, "idx"), {"idx", "i"}) ][sort], Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], group = Table.Group(Source, "ID", {"x", fx_seq}), z = Table.Combine(group[x]) in z
