Forum Discussion
stribor45
11 months agoPost Prodigy
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...
- 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"
stribor45
11 months agoPost Prodigy