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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors