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

 

IDScoreType 1Type 2
11111133INTNOCOMP
11111144NOTYPEAZ
11111155NOTYPEAZ
11111166NOTYPEAZ
11111177NOTYPEAZ
22222232NOTYPEAZ
22222272NOTYPEAF
    
IDScoreType 1Type 2
11111133INTNOCOMP
22222272NOTYPEAF

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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"

     

     

     

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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"

     

     

     

  • jgeddes's avatar
    jgeddes
    Super User
    11 months ago

    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...