How to apply this logic in Power query

[Sriku]

Hi, 

In the data I want to apply in power query . It tell how many consectuive count of red. Need help

UC ID	Date	Overall Flag	Red Flag
C1	31-Jan-20	Red	1
C1	29-Feb-20	Green	0
C1	31-Mar-20	Green	0
C1	30-Apr-20	Green	0
C1	31-May-20	Red	1
C1	30-Jun-20	Green	0
C1	31-Jul-20	Green	0
C1	31-Aug-20	Green	0
C1	30-Sep-20	Green	0
C1	31-Oct-20	Red	1
C1	30-Nov-20	Red	2
C1	31-Dec-20	Red	3
C2	31-Jan-20	Red	1
C2	29-Feb-20	Red	2
C2	31-Mar-20	Red	3
C2	30-Apr-20	Green	0
C2	31-May-20	Green	0
C2	30-Jun-20	Green	0
C2	31-Jul-20	Red	1
C2	31-Aug-20	Red	2
C2	30-Sep-20	Red	3
C2	31-Oct-20	Red	4
C2	30-Nov-20	Red	5
C2	31-Dec-20	Red	6

[amitchandak]

@ImkeF , can you help on this

[Sriku]

Can any one please help on this 

[Sriku]

If not in power query. Any other method to solve this. Basically for each "UC id"  need to count how many  consequetive occurance of red

[ImkeF · Accepted solution]

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-powe... and for the nested index: https://www.youtube.com/watch?v=-3KFZaYImEY

[Sriku]

Thanks for your response. Can you please share the Powerbi working. Need to understand more since I am a learner 

[ImkeF]

Yes sure. 
But could also just paste the code into the advanced editor of a new query and have everything there is.

[Sriku]

Thanks a Lot for Sharing. Let me try and check.

[Sriku]

Is other query table are relevant to the table query or I can delete those query table

[ImkeF]

You can delete them. Meant to upload a cleaned version.