Home > Enterprise >  Filter out rows on multiple criteria Power Query
Filter out rows on multiple criteria Power Query

Time:05-03

I am using office 365 and Power query

I have a table with 1 column and I want to filter out multiple rows

I can filter on a single item

#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Column1] <> "Id")

How do I filter on multiple items some of which might be missing depending on my project

My attempt to cobble this together from multiple code snippets

#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Column1] <> {"Id","Type","Label","first Name","Last Name","Description","Segment","Image","Project Name","Email","Email 2","Email 3","sumApp Link", "Initial Date", "Last Date", "Master ID"", MissingField.Ignore),

If this is not possible is there a different/better way to do this?

Edit:

enter image description here

enter image description here

let  
    sourceLink = Excel.CurrentWorkbook(){[Name="sourceLink"]}[Content]{0}[Column1],
    Source = Json.Document(Web.Contents(sourceLink)),
    Expanded=Table.FromRecords (Source[elements] ),
    #"Demoted Headers" = Table.DemoteHeaders(Expanded),
    #"Kept First Rows" = Table.FirstN(#"Demoted Headers",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    exclude_list={"Id","Type","Label","First name","Last Name","Description","Segment","Image","Project Name","Email","Email 2","Email 3","sumApp Link", "Initial Date", "Last Date", "Master ID"}, 
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each not List.ContainsAny({[Column1]},exclude_list ,Comparer.OrdinalIgnoreCase ))
in
    #"Filtered Rows"

CodePudding user response:

Note M is case sensitive, so "first Name" is not the same as "First Name"

#"Filtered Rows" = Table.SelectRows(#"Added Index", each not List.ContainsAny({[Column1]},
{"Id","Type","Label","first Name","Last Name","Description","Segment","Image","Project Name","Email","Email 2","Email 3","sumApp Link", "Initial Date", "Last Date", "Master ID"}
))

If you want to ignore case you can use below, which also shows how you can break out the exclude list

// get the exclude list from another table
exclude_list=Table2[Column1], 

or

// specify the exclude list
exclude_list={"Id","Type","Label","first name","Last Name","Description","Segment","Image","Project Name","Email","Email 2","Email 3","sumApp Link", "Initial Date", "Last Date", "Master ID"}, 

then

#"Filtered Rows" = Table.SelectRows(#"Added Index", each not List.ContainsAny({[Column1]},exclude_list ,Comparer.OrdinalIgnoreCase ))
  • Related