Forum Discussion
Updating rows based on earlier row
Hi all, I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source. The solution...
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
zThanks 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.
