Group by ID then filter based on multiple column

[stribor45]

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 the date and result look like

ID	Score	Type 1	Type 2
111111	33	INT	NOCOMP
111111	44	NOTYPE	AZ
111111	55	NOTYPE	AZ
111111	66	NOTYPE	AZ
111111	77	NOTYPE	AZ
222222	32	NOTYPE	AZ
222222	72	NOTYPE	AF
ID	Score	Type 1	Type 2
111111	33	INT	NOCOMP
222222	72	NOTYPE	AF

[ronrsnfld · Accepted solution]

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"

[jgeddes]

Try this...

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"}, {{"AllRows", each _, type table [ID=nullable number, Score=nullable number, Type 1=nullable text, Type 2=nullable text]}}),
    #"Select Nested Rows" = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.SelectRows(_, each if [Type 1] = "INT" then true else if [Type 2] = "AF" then true else false), type table [ID=nullable number, Score=nullable number, Type 1=nullable text, Type 2=nullable text]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Select Nested Rows", "AllRows", {"Score", "Type 1", "Type 2"}, {"Score", "Type 1", "Type 2"})
in
    #"Expanded AllRows"

Start...

End...

[stribor45]