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