Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
11 months ago
Solved

Group by ID then filter based on multiple column

I am basically trying to filter rows based on this group by ID keep records where for this id Type 1 is "INT" if for this id the Type 1 is not INT  keep row where Type 2 is AF This is what...
  • ronrsnfld's avatar
    11 months ago

    A little simpler:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQDJR0lY2Mg4ekXAiT9/J39fQOUYnWQpE1MwBIhkQGuQIZjFKqsqSk+WTMzfLLm5lhkjcAA5CwjfLLmqLJuSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Score = _t, #"Type 1" = _t, #"Type 2" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"ID", Int64.Type}, {"Score", Int64.Type}, {"Type 1", type text}, {"Type 2", type text}}),
       
        #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
            {"all", each Table.SelectRows(_, each [Type 1]="INT" or [Type 2]="AF")  , 
            type table [ID=nullable number, Score=nullable number, Type 1=nullable text, Type 2=nullable text]}}),
       
        #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Score", "Type 1", "Type 2"})
    in
        #"Expanded all"