Forum Discussion
Grouping Problem
Hello, ChandeepChhabra I don't think you'll solve this with Table.Group and it's 5th agrument alone. Consider index column to control number of rows in your current group.
let Source = Excel.CurrentWorkbook(){[Name="WinLossWDates"]}[Content], idx = Table.AddIndexColumn(Source, "idx"), group = Table.Group( idx, {"WinLoss", "Date", "idx"}, {"Streak", Table.RowCount}, GroupKind.Local, (s, c) => Number.From( s[WinLoss] <> c[WinLoss] or Duration.Days(c[Date] - s[Date]) <> (c[idx] - s[idx]) ) ) in groupLets start with first 2 rows:
We start with
State = [Winloss = "Win", Date = #date(2024, 01, 01)]
and we compare this record to the very next record
Current = [Winloss = "Loss", Date = #date(2024, 01, 01)]
Do we need to start new group here? YES, because State[Winloss] <> Current[Winloss].
And we start new group with
State = [WinLoss = "Loss", Date = #date(2024, 01, 01)] and check for the very next record which is
Current = [WinLoss = "Loss", Date = #date(2024, 02, 01)]. It's "Current (1)" over here:
Do we need to start new group at Current (1)? NO, because State[Winloss] = Current[Winloss] and duration between Current[Date] and State[Date] is 1 day exactly. We keep grouping so that "State" stays the same! It always stays the same withing the group!
Next step: we stay in the same group with the same "State" but the very next "Current" becomes our next record
Current (2) = [WinLoss = "Loss", Date = #date(2024, 03, 01)]. Again, "State" is the same as we are in the same group. We need to make a decision: start new group or not? WinLoss field is same but what about Date? At this point we have State[Date] (which is Jan 1st) and Current[Date] (which is Jan 3rd). We don't have info about previous row with Date = Jan 2nd anymore but we need to make a decision.
That's why we need some helper column in the form of index to calculate something like "running total" between "State" and "Current" to compare it to duration in days in this particular case.
The key is that the only information we have at each stage of iteration is just the very first ("state") row/record and current row. Iterator moves on. So that at each step the arguments of our desicion making function (5th argument) are 2 rows/records: the very first one and the current one. Decision is always the same: do we need to start new group or stay in the current group with the same "State" record and next "Current" record.
Hello, ChandeepChhabra I don't think you'll solve this with Table.Group and it's 5th agrument alone. Consider index column to control number of rows in your current group.
let
Source = Excel.CurrentWorkbook(){[Name="WinLossWDates"]}[Content],
idx = Table.AddIndexColumn(Source, "idx"),
group = Table.Group(
idx,
{"WinLoss", "Date", "idx"},
{"Streak", Table.RowCount},
GroupKind.Local,
(s, c) => Number.From(
s[WinLoss] <> c[WinLoss] or
Duration.Days(c[Date] - s[Date]) <> (c[idx] - s[idx])
)
)
in
group
I understand the solution but can you explain the reason for adding an index and why can't it work without the index?
Basics: 5th argument of Table.Group is a function of 2 arguments. I usually call them "s" (state) and "c" (current). "State" is first record (table row) in the group of records and consists of fields listed in 2nd argument. "Current" is current record (with same list of fields) that we compare to "state" in our function. If function evaluates to 1 then new group is started. As you see we don't have an access to the whole group of records. E.g. we can't calculate current number of rows in it, we don't have an access to previous (last) record in group etc. Just the very first (s) and current (c) records. We can calculate duration in days between "state" and "current". But how do we know if it's time to start new group? We need to compare this number of days with smth else that we don't have (like number of rows in our group). That's why I add index to compare duration with difference in index values.
Sorry I didn't understand. Here is my confusion.
- Can you point out which exact rows are S and C referring to as the iterations progresses?
- Even in your logic why can't we compare Duration.Days output to a single value like 1?
Thank you so much for your time 🙏
