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