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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

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

    • 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