Forum Discussion
Solved
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 i...
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
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
Hi, sorry for my late response. The code fits perfectly and calculates the loops amazingly fast. I am thrilled! Many thanks for the support.