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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors