categorize the groups based on some logic

[rashidanwar]

I have a table grouped by id. Now I want to add a new column "local" filled bay a value Yes or no based on a condition.
Condition is that whenever there is first instance of event C and there is the type local against that event then in the new column local fill the value "Yes" for all the records of the id category. Please note that Event C could have multiple instances in the group. So need to consider only first instance.  
id       event        type
1        A               local
1        B               null
1        C               null
1        C               local
2        A               null
2        C               local
2        B               external
2        C               external
3        A              local
3        C              local
4        B              external
4        C              external
4        C              Local

Output table will look like
id       event        type           local
1        A               local          No
1        B               null            No
1        C               External     No
1        C               local          No
2        A               null            Yes
2        C               local          Yes
2        B               external     Yes
2        C               external     Yes
3        A              local           Yes
3        C              local           Yes
4        B              external      No
4        C              external      No
4        C              local           No

I would appreciate any help in this regard.
Thank you!
Rashid

[rashidanwar]

Thank you! @Vijay_A_Verma 

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pz85MQcpVgdiIgTEOeV5iAEnLEJIPQYQU2BKzHCqgRkbGpFSWpRXiKqMhRBYwwnGWOYZoLNNBNspsEEfSB6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, event = _t, #"type" = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([event] = "C")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"id"}, {{"Temp", each _, type table [id=nullable text, event=nullable text, type=nullable text]}}),
        //Function Start
    fxProcessNext=(Tbl)=>
        let
            #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 and [type]="local" then "Yes" else "No")
        in
            #"Added Custom",
    //Function End
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])),
    #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Custom"}, {"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded fxProcessNext", each ([Custom] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Temp"}),
    #"Merged Queries" = Table.NestedJoin(Source, {"id"}, #"Removed Columns", {"id"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Custom"}, {"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Removed Columns",null,"No",Replacer.ReplaceValue,{"Custom"})
in
    #"Replaced Value"