Is it possible dynamically pass a list of values to filter Power Query?

[DMfba]

Hi Everyone,

I have been searching for a while on this topic. Is it possible to allow the end user to enter multiple values into an excel table, send it to power query and filter the source data (Main Query) by the entire list? I was only able to do this with a single value but not multiple values at once.

Here is what I am trying to set up. I have a report that uses multiple queries to show customer sales and various information. There is a unique ID for each customer location. Depending on the request, the end user will need to pull all locations, some locations, or only one location. Example, if the user enters ID #’s 4,6, and 12, I would like power query to filter the ID field for all three ID #s.

I made a list query called “VList” out of the users entry (excel table), then called that query in the “Main Query” where the ID is filtered. However, it returns an empty table. If I change VList to point to a single cell in the excel table, it works fine.

The M code that was used:

VList – takes the excel table and drills down to a list​

Table.SelectRows(#"Removed Other Columns", each ([#"Vend#"] = VList))​

In theory I need the following M Code to be dynamic and not hard coded.

Table.SelectRows(#"Removed Other Columns", each ([#"Vend#"] = 4 or [#"Vend#"] = 6 or [#"Vend#"] = 12))​

How do we tell Power Query to take every record in the list and check if each row in my main query is equal to a value in the list? Please let me know if anyone has any suggestions or attempted to this in the past? Thank you all for your help.

 

[JB2020]

If I understand correctly, it sounds like you could you use List.Contains()

Table.SelectRows(#"Removed Other Columns", each List.Contains(VList, [#"Vend#"] ))

List.Contains - PowerQuery M

Learn more about: List.Contains

docs.microsoft.com

 

[DMfba]

Hi JB,

Thant worked perfectly for what I was trying to test. I'm not sure yet if the End Result will be better in Power Pivot or not yet, as I may need to add PV tables instead of loading the data table to the workbook. Thank you for the help I was looking for a way to do this for a few weeks.

Sorry If initial question was not straight forward. If this helps future users with the same issue, here is the simple version. If you have a query with 2 columns (Customer ID & Sales), you might have a long list of 50,000 customers. I wanted a way for the end user to enter a list of values they wanted to look at so they don't have to load all 50,000 customer info.

List.Contrains - is the answer.

Thank you Again

DM

 

[DMfba]

Hi JB,

I am curious if there is a way to add an error check to the list contains function? Some of the tables are small, meaning they won't the number the user entered (which is a good thing in this case). Is there a way to return a blank table if the Vend# ( user entered number) does not match any of the Vend#'s in the table?

Thank You,
DM

 

[JB2020]

Hi JB,

I am curious if there is a way to add an error check to the list contains function? Some of the tables are small, meaning they won't the number the user entered (which is a good thing in this case). Is there a way to return a blank table if the Vend# ( user entered number) does not match any of the Vend#'s in the table?

Thank You,
DM

Hi DM

If none of the Vend#s are contained in the list then it will just filter out all the rows. This shouldn't cause any errors. You could add in a step at the end if you want to change the output if there are no rows, something like:

FinalStep = if Table.RowCount(#"Filtered Rows") = 0 then "No matches" else #"Filtered Rows"

I may have misunderstood your question though

 

[DMfba]

HI JB,

Thank you for the quick reply. I gave your suggestion a shot and added that code as the next step after list contains. However, it came back with an error that I'm not sure how to correct. I am still a bit new to creating my own M code.

The error is:

Expression.Error: The import Filtered Rows matches no exports. Did you miss a module reference?

Do I need to a reference somewhere before this step?

 

[JB2020]

HI JB,

Thank you for the quick reply. I gave your suggestion a shot and added that code as the next step after list contains. However, it came back with an error that I'm not sure how to correct. I am still a bit new to creating my own M code.

The error is:

Expression.Error: The import Filtered Rows matches no exports. Did you miss a module reference?

Do I need to a reference somewhere before this step?

Are you able to share the query that is triggering the error?

 

[DMfba]

Are you able to share the query that is triggering the error?

Hi JB,

This was a complete Rookie Mistake. I misunderstood and thought the 2nd #"Filtered Rows" was calling something. Once I changed both of them to #"List Contains" which was the function in the previous step it worked fine.

 #"FinalStep" = if Table.RowCount(#"Filtered Rows") = 0 then "No matches" else #"Filtered Rows",

I only changed the portion where is calls for the last step. It should read

#"FinalStep" = if Table.RowCount(#"List Contains") = 0 then "No matches" else #"List Contains",

Thank you for your help.
DM