Updating rows based on earlier row

[PaulBoden]

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 needs to:

• Find instances where a flag has indicated a manual adjustment has been made to a user entry
• Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable)
• Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment.

Considering the attached table, I’ve tried to outline the logic I’m trying to implement below:

For the purposes of this explanation, I’ll refer to the manual adjustment as row X.

"IF Flag = “Adj” (

                IF Case = “L” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Output = “H”

                )

                IF Case = “M” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Level = X.Level

                )

)"

Any advice on how to optimally achieve this within PowerBI would be greatly appreciated.

ID	Case	Flag	Level	Date	Output
1	HC	Source	1	03/07/2024	S
1	HC	Source	1	04/07/2024	S
1	L	Adj	1	05/07/2024	S
1	HC	Source	1	06/07/2024	S
1	HC	Source	1	07/07/2024	S
1	L	Source	1	08/07/2024	S
1	HC	Source	1	09/07/2024	S
1	HC	Source	1	10/07/2024	S
1	HC	Source	1	11/07/2024	S
1	HC	Source	1	12/07/2024	S
2	HC	Source	3	03/07/2024	S
2	HC	Source	3	04/07/2024	S
2	HC	Source	3	05/07/2024	S
2	HC	Source	3	06/07/2024	S
2	HC	Source	3	07/07/2024	S
2	L	Source	3	08/07/2024	S
3	HC	Source	3	03/07/2024	S
3	HC	Source	3	04/07/2024	S
3	HC	Source	3	05/07/2024	S
3	HC	Source	3	06/07/2024	S
3	HC	Source	3	07/07/2024	S
3	L	Adj	3	08/07/2024	S
3	HC	Source	3	09/07/2024	S
3	HC	Source	3	10/07/2024	S
3	HC	Source	3	11/07/2024	S
3	HC	Source	3	12/07/2024	S
4	HC	Source	5	03/07/2024	S
4	HC	Source	5	04/07/2024	S
4	L	Adj	5	05/07/2024	S
4	HC	Source	5	05/07/2024	S
4	HC	Source	5	06/07/2024	S
4	HC	Source	5	07/07/2024	S
4	HC	Source	5	08/07/2024	S
4	HC	Source	5	09/07/2024	S
4	HC	Source	5	10/07/2024	S
4	HC	Source	5	11/07/2024	S
4	L	Source	5	12/07/2024	S
5	HC	Source	2	03/07/2024	S
5	M	Adj	3	04/07/2024	S
5	HC	Source	2	04/07/2024	S
5	HC	Source	2	05/07/2024	S
5	HC	Source	2	06/07/2024	S
5	HC	Source	2	07/07/2024	S
5	HC	Source	2	08/07/2024	S
5	M	Source	3	09/07/2024	S
5	HC	Source	3	10/07/2024	S
5	HC	Source	3	11/07/2024	S
5	HC	Source	3	12/07/2024	S
5	HC	Source	3	13/07/2024	S

[dufoq3]

@PaulBoden, could you provide expected result based on sample data?

[PaulBoden]

"Character restriction prevents me from showing the full desired output but I've included a sample below for ID 4 and 5."

ID	Case	Flag	Level	Date	Output
4	HC	Source	5	03/07/2024	S
4	HC	Source	5	04/07/2024	S
4	L	Adj	5	05/07/2024	S
4	HC	Source	5	05/07/2024	H
4	HC	Source	5	06/07/2024	H
4	HC	Source	5	07/07/2024	H
4	HC	Source	5	08/07/2024	H
4	HC	Source	5	09/07/2024	H
4	HC	Source	5	10/07/2024	H
4	HC	Source	5	11/07/2024	H
4	L	Source	5	12/07/2024	S
5	HC	Source	2	03/07/2024	S
5	M	Adj	3	04/07/2024	S
5	HC	Source	3	04/07/2024	S
5	HC	Source	3	05/07/2024	S
5	HC	Source	3	06/07/2024	S
5	HC	Source	3	07/07/2024	S
5	HC	Source	3	08/07/2024	S
5	M	Source	3	09/07/2024	S
5	HC	Source	3	10/07/2024	S
5	HC	Source	3	11/07/2024	S
5	HC	Source	3	12/07/2024	S
5	HC	Source	3	13/07/2024	S

[PaulBoden]

@AlienSx @dufoq3  Apologies for pestering - I appreciate all the support so far and I'm very close to a working solution. The outputs look good but I'm getting a number of false positives as well.

Are you able to advise me on how I can update the proposed logic to specifically target records following a manual adjustement. The logic is intended to achieve the below:

If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L"".

The second part of the logic ([Case] != "L") is because eventually the golden source will update and a record will be ingested containing x.[ID], x.[Case] and [Flag] = Source.

Any advice would be greatly appreciated

[dufoq3]

Hi @PaulBoden, for ID 4 check this:

You've mentioned:

If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L""., but in your sample output there is also H as output for row nubmer 4 where the date equals [Flag] = "Adj" date. You cand edit this in GroupedRows step if necessary.

Regarding ID 5, you have to explan logic for which rows you want to change Level.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldOxCsIwFEbhd8lcaJs0VkdxcdDJsXRSFxdB8P1thQ7puT9eB0X048rtSYYhtKEKx8P0dnm+X9f79GH+pkl109exid38SxgrCTsTnqbX/vZYUPZO23hhL/+2dFvvwJ0Tto0Xtl4YASNgsqMIyCgCMoyADCMgw8R1mGSHSd6lBeTSAnJpAbm0gFw6FZfgv415FG1oHEUBeRQF5FHsALNdRUBW6YpHk+0iYpobMp2ATCcg8wnIfDY08gnIfMszLB3rZQyMdr0ZnsvzynJimhuynoCsJyDrCch6y8a/7h4HirsnIOMJ6Kn3hat64wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Case = _t, Flag = _t, Level = _t, Date = _t, Output = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Level", Int64.Type}, {"Date", type date}}, "sk-SK"),
    Filtered_ID_4_Only = Table.SelectRows(ChangedType, each ([ID] = 4)),
    GroupedRows = Table.Group(Filtered_ID_4_Only, {"ID"}, {{"All", each _, type table}, {"Transformed", each 
        [ a = Table.SelectRows(_, (x)=> x[Flag] = "Adj" and x[Case] = "L")[Date]{0}?,
          b = Table.AddColumn(_, "New Output", (x)=> try if x[Date] >= a and x[Case] <> "L" then "H" else x[Output] otherwise x[Output], type text)
        ][b], type table }}),
    CombinedTransformed = Table.Combine(GroupedRows[Transformed])
in
    CombinedTransformed

[AlienSx]

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

[PaulBoden]

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. 

[AlienSx]

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. 

[PaulBoden]

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

[PaulBoden]

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.

[bhanu_gautam]

@PaulBoden , We can try to create a new calculated column based on logic provided by you

AdjustmentOutput =
VAR CurrentID = 'YourTable'[ID]
VAR CurrentDate = 'YourTable'[Date]
VAR CurrentCase = 'YourTable'[Case]
VAR CurrentFlag = 'YourTable'[Flag]
VAR CurrentLevel = 'YourTable'[Level]

RETURN
IF (
CurrentFlag = "Adj",
IF (
CurrentCase = "L",
"H",
IF (
CurrentCase = "M",
CurrentLevel,
BLANK()
)
),
IF (
'YourTable'[Date] >= CALCULATE(MIN('YourTable'[Date]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "L")),
"H",
IF (
'YourTable'[Date] >= CALCULATE(MIN('YourTable'[Date]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "M")),
CALCULATE(MAX('YourTable'[Level]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "M")),
'YourTable'[Output]
)
)
)