How to apply this logic in Power query

Sorry Sriku  for late reply, too busy currently.

You can use the 5th element of the Group function in combination with a nested index like so:

 

 

let
  Source = Table.FromRows(
      Json.Document( Binary.Decompress( Binary.FromText(
                  "fZDNCsIwEAbfJecGkq0KHouiEFBBjyWHWoMXiaUYoW9vpFbz4+4lDQyTfkxds5VkBSslV43lIPz9aC7+lEwXHwhLvjHnEW57Y6z/ih/27q7pcSx41RH4bQ/In72rnKVc5W4UrtyVGnYyHWUf2gc+bH9/hhAic23aEJYjBKo0JKWjZyHpnD+LV/66A47xztPmqXO2Oaycbw4a/0uRFJ5FZlJ4HplJ4QXT+gU=", BinaryEncoding.Base64 ),  Compression.Deflate ) , 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table[#"UC ID" = _t, Date = _t, #"Overall Flag" = _t, TargetValue = _t]
    ),

  #"Grouped Rows" = Table.Group(
      Source, 
      {"UC ID", "Overall Flag"}, 
      {{"All", each Table.AddIndexColumn(_, "GeneratedResult", 1, 1)}}, 
      GroupKind.Local, 
      (x, y) => Number.From(x <> y)
    ),
  #"Expanded All" = Table.ExpandTableColumn(
      #"Grouped Rows", 
      "All", 
      {"Date", "TargetValue", "GeneratedResult"}, 
      {"Date", "TargetValue", "GeneratedResult"}
    ),
  #"Added Custom" = Table.AddColumn(
      #"Expanded All", 
      "FinalResult", 
      each if [Overall Flag] = "Red" then [GeneratedResult] else 0
    ),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"GeneratedResult"})
in
  #"Removed Columns"

 

 

Some background on how this works:

5th element:  https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-power-query/ and for the nested index: https://www.youtube.com/watch?v=-3KFZaYImEY