Forum Discussion

PaulBoden's avatar
PaulBoden
Helper I
2 years ago

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

 

 

 

IDCaseFlagLevelDateOutput
1HCSource103/07/2024S
1HCSource104/07/2024S
1LAdj105/07/2024S
1HCSource106/07/2024S
1HCSource107/07/2024S
1LSource108/07/2024S
1HCSource109/07/2024S
1HCSource110/07/2024S
1HCSource111/07/2024S
1HCSource112/07/2024S
2HCSource303/07/2024S
2HCSource304/07/2024S
2HCSource305/07/2024S
2HCSource306/07/2024S
2HCSource307/07/2024S
2LSource308/07/2024S
3HCSource303/07/2024S
3HCSource304/07/2024S
3HCSource305/07/2024S
3HCSource306/07/2024S
3HCSource307/07/2024S
3LAdj308/07/2024S
3HCSource309/07/2024S
3HCSource310/07/2024S
3HCSource311/07/2024S
3HCSource312/07/2024S
4HCSource503/07/2024S
4HCSource504/07/2024S
4LAdj505/07/2024S
4HCSource505/07/2024S
4HCSource506/07/2024S
4HCSource507/07/2024S
4HCSource508/07/2024S
4HCSource509/07/2024S
4HCSource510/07/2024S
4HCSource511/07/2024S
4LSource512/07/2024S
5HCSource203/07/2024S
5MAdj304/07/2024S
5HCSource204/07/2024S
5HCSource205/07/2024S
5HCSource206/07/2024S
5HCSource207/07/2024S
5HCSource208/07/2024S
5MSource309/07/2024S
5HCSource310/07/2024S
5HCSource311/07/2024S
5HCSource312/07/2024S
5HCSource313/07/2024S
Need Help

10 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

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

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
    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's avatar
      PaulBoden
      Helper I
      2 years ago

      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's avatar
        AlienSx
        Super User
        2 years ago

        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's avatar
      PaulBoden
      Helper I
      2 years ago

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

       

      IDCaseFlagLevelDateOutput
      4HCSource503/07/2024S
      4HCSource504/07/2024S
      4LAdj505/07/2024S
      4HCSource505/07/2024H
      4HCSource506/07/2024H
      4HCSource507/07/2024H
      4HCSource508/07/2024H
      4HCSource509/07/2024H
      4HCSource510/07/2024H
      4HCSource511/07/2024H
      4LSource512/07/2024S
      5HCSource203/07/2024S
      5MAdj304/07/2024S
      5HCSource304/07/2024S
      5HCSource305/07/2024S
      5HCSource306/07/2024S
      5HCSource307/07/2024S
      5HCSource308/07/2024S
      5MSource309/07/2024S
      5HCSource310/07/2024S
      5HCSource311/07/2024S
      5HCSource312/07/2024S
      5HCSource313/07/2024S
      • PaulBoden's avatar
        PaulBoden
        Helper I
        2 years ago

        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