Forum Discussion
Updating rows based on earlier row
let
Source = Table.Sort(your_table, {"ID", "Date"}),
rec = (x) => [L = {"Output", each "H"}, M = {"Level", each x{0}[Level]}],
fx = (tbl) => Table.TransformColumns(tbl, Record.FieldOrDefault(rec(tbl), tbl{0}[Case], {})),
group = Table.Group(
Source,
{"ID", "Case"},
{"x", fx},
GroupKind.Local,
(s, c) => Number.From(s[ID] <> c[ID] or List.Contains({"L", "M"}, c[Case]))
),
z = Table.Combine(group[x])
in
z
Thanks for your comment.
I suspect this probably close to the optimal approach but I'm struggling to implement it correctly. Currently I'm seeing the following error:
Expression.Error: A cyclic reference was encountered during evaluation.
Could you please expand on what the above is doing and why so I can identify the problem?
Thanks in advance for the help.
create blank query, replace it's content with my code and change your_table in my code to the name of your table. Looks like you are trying to add new (conditional) column - don't do that. Watch this.
Yes - I subsequently realised thanks. I was attempting to execute as a transformation within the existing table. Thanks again.
Will confirm once I've got it working.
Hi - The outputs not quite I was expecting. I can't see any reference in your code to 'Flag' so I was wondering this this could be because the transformation is being applied to all records, regardless of whether they are from source or a manual adjustment (adj)?
Also, I was wondering if this could be achieved as part of a transformation step within an existing table? I ask because the source table referenced is quite large and already requires several steps to be applied - I wonder if it would be more performative to execute the above within the original query rather than as a stand-alone?
If you believe this is a viable approach, any advice on how I would modify the query to integrate it correctly would be greatly appreciated! Thanks