Forum Discussion

rashidanwar's avatar
rashidanwar
Advocate II
4 years ago
Solved

categorize the groups based on some logic

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...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"