Power Query Filtering from a List

[crawfe]

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"

[watkinnc]

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

[SOppeneer]

@watkinnc Thanks for your suggestion. I used it in my query, but unfortunately I'm still getting the error "Expression.Error: Evaluation resulted in a stack overflow and cannot continue.". I suppose this is because the list being referenced has nearly 20,000 records. 

Is there a way to filter a table based on a list of such magnitude, or are there other (best) practices to get the desired result? 

[_AlexandreRM_]

Hello @SOppeneer , in my opinion you should use an inner join to perform the filter.

This is pseudo code because I can't get Power Query right now, but you can process as follows.

In your table "Table1" to filter you have n columns, and a column called "Material" which you want to filter. You have also a table "Table2" (if a list you will need to convert it to a table) in which are all values you want to keep, in a column named exactle the same ("Material"). Table2 must not containing duplicates.

Then use this function:

filtered = Table.Join(Table1, {"Material"}, Table2, {"Material"}, JoinKind.Inner)

Doc of the function: https://learn.microsoft.com/fr-fr/powerquery-m/table-join

The purpose of the function is to only keep matches between the 2 tables (because it's an inner join), and append all additional columns from the 2nd table to the 1st one. Here there is no additional columns in the 2nd table (we exclude the "Material" column because it's a key column), so your result will be the 1st table filtered on all Materials contained in the 2nd.

Hope this helps.

[SOppeneer]

@_AlexandreRM_ Thanks! The inner join enabled me to load the filtered data. The only disadvantage is that now first the entire dataset needs to be loaded before the "filter" is applied, in my case 3 million records for 30 thousand results (1%). Of course this is preferable over no results at all. 🙂

Would there be a way to filter such dataset within a single query to only load the filtered results and potentially enable incremental refresh?  

[_AlexandreRM_]

Hello @SOppeneer , I don't think so (but I may be wrong).

But natively, PBI folds queries when possible. in your example, I think the request to the Table1 database might already be containing the inner join with the Table2 values.

Do you know if you can track queries in your remote database? If yes you can just check if the query already contains the inner join instruction.

[SOppeneer]

@_AlexandreRM_ I suppose the reason for the query not getting folded is that the scope records are taken from an Excel file on SharePoint, while the dataset itself is from SAP HANA. Anyway, I got the data I needed, so thanks for that!

[crawfe · Accepted solution]

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

[watkinnc]

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