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:
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 ))