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

DMfba

New Member
Joined
Mar 21, 2019
Messages
25
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
If I understand correctly, it sounds like you could you use List.Contains()

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

 

DMfba

New Member
Joined
Mar 21, 2019
Messages
25
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

New Member
Joined
Mar 21, 2019
Messages
25
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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

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

I may have misunderstood your question though
 

DMfba

New Member
Joined
Mar 21, 2019
Messages
25
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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 21, 2019
Messages
25
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.

Power Query:
 #"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
Power Query:
#"FinalStep" = if Table.RowCount(#"List Contains") = 0 then "No matches" else #"List Contains",

Thank you for your help.
DM
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,033
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top