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

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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