cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
crawfe
Helper I
Helper I

Power Query Filtering from a List

Hello. I am relatively new to power BI. In power query, I Import data from a very large table and use the built-in column filters. Right now, I only have 4 criteria. This works fine but eventually I might get to 15-20 and I don't want to have 20 OR statements. How can I change the code below to allow me to filter from a list here? Even better, can the list be in a separate table?  Thanks!

 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [MATERIAL] = "111111" or [MATERIAL] = "2222" or [MATERIAL] = "33333" or [MATERIAL] = "444444")
in
#"Filtered Rows1"

1 ACCEPTED SOLUTION
crawfe
Helper I
Helper I

So in this case: where GROUP is the other table name and NumID is the column name; and I am at the Filter1 step. 

This Filters statement is inserted as-is in the Advanced editor query?

And to emphasize your point, the column must have distinct values? (I will try it when my system comes back up 😞

 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each List.Contains(List.Buffer(GROUP[NumID]), [Material]))

View solution in original post

3 REPLIES 3
watkinnc
Super User
Super User

So then you would "Insert Step After", rename that step "Filters", and the copy and paste the following into the formula bar (overwrite whatever is currently in the window:

 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each List.Contains(List.Buffer(GROUP[NumID]), [Material]))

I mentioned the list being distinct; it's not strictly necessary for the list to be distinct, but your query will run much faster if you do make the list distinct:

 

Table.SelectRows(#"Filtered Rows", 

each List.Contains(List.Buffer(List.Distinct(GROUP[NumID])), [Material]))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
crawfe
Helper I
Helper I

So in this case: where GROUP is the other table name and NumID is the column name; and I am at the Filter1 step. 

This Filters statement is inserted as-is in the Advanced editor query?

And to emphasize your point, the column must have distinct values? (I will try it when my system comes back up 😞

 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each List.Contains(List.Buffer(GROUP[NumID]), [Material]))

View solution in original post

watkinnc
Super User
Super User

Yes indeed.  If you have a list as another query, and that is the list that you are wanting to use for filtering, and that list is named, say, ValuesToFind, and it's already distinct:

 

Filters = Table.SelectRows(#"Filtered Rows", each List.Contains(List.Buffer(ValuesToFind), [Material]))

 

If you have a table column with the values used to filter(as opposed to an actual list), replace ValuesToFind with FilterTable[FilterColumn].

 

--Nate 

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors