Power Query parameters | Add flexibility to your queries | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post ★

★ About this video ★
This video shows how to use parameters in Power Query to change the output of a query.

0:00 Introduction
1:12 Types of parameters
1:55 Scenario
4:33 Create cell parameters
7:35 Using parameters
8:16 Testing the parameters
8:58 Official Power Query parameters
11:49 Summary

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

[Koniogdy]

Another precious brick in my PQ wall :)
Thanx Mark!

[jamesdickens]

Thank you so much for this video. This helped me crack the code to a problem I have had for a while and couldn't resolve. With a couple of extra steps I can now select data as you show in the video but I have set it up to allow me to edit that value and update the table with the new value so when next I pull up data for say "John" the value I put in as a new amount now shows up in "John's table. Not sure if I'm making sense with this but this really helps me out on being able store and update values without using VBA to do it.

[munim]

Thank you for a great explanation. Can you please make a video to cover the firewall error? They are annoying!

[serdip]

Brilliant video! Thank you so much for explaining both techniques for creating and using parameters to make queries return dynamic results based on the value(s) of the parameters(s) it is constructed to work with.

I encountered the dreaded formula firewall error in which PQ complains about the data source of the parameter being different than the data source of the query, or something like that. From my research on the web, one solution is to select the "Always Ignore Privacy Level" option in the Query Options dialog under both the GLOBAL and CURRENT WORKBOOK sections within Power Query (File->Options and settings->Query Options). I imagine as long as the data sources are local to the user's computer, selecting this option poses no serious security risks.

Thanks again for producing a very helpful and clearly explained presentation on this important topic.

[zaighamuddinfarooqui]

Excellent. Making life more easier. Thanks Mark. 👍👍💕💕

[nataliaartimenia]

thank you so much, you've helped me a great deal with my parameter settings!

[simonsmith]

worked a treat. Wondering if I want to see all the data (ie no filter) how to do that without going back to power query editor? Thanks again

[iankr]

Many thanks for the clear explanation, Mark.

[excel_ladz]

Thank you for this video! I'm using Excel on Mac, and unfortunately I'm getting "An on-premises data gateway is required to connect." error in the Power Query Editor. Is there a way to fix this?

[satayhot]

Nice! you explained clearly parameters that in the excel worksheet vs parameters in PQ. I do understand now why all query and parameter should not be only kept in PQ rather than cells in excel. For me, this is especially useful of migrating all the m codes with parameters rather than has to take keep the parameter in a spreadsheet cell as this make the spreadsheet clunky. The role of spreadsheet should only keep the data tables

[gonzuic]

Thanks Mark.
You said, Power Query parameters can help with “incremental refresh”, do you have some examples about that?

Gracias.

[serdip]

Great video with lots of very useful and practical information that makes Power Query even more powerful and dynamic.

I noticed in this and similar videos that the scenario for implementing worksheet cells as parameters typically involves filtering a table. While this is certainly valid and makes for an easily understandable introduction to the concepts, it seems that the AutoFilter dropdowns offer the same functionality. If so, would a user typically implement parameters in this fashion?

To understand and put into practice the concepts taught in the video, I tried to do something slightly different. I created three identical tables with data from each of three different divisions of a fictitious company. I created a table called "Select_Source" with a dropdown populated with three values: "Division_1", "Division_2", "Division_3".

Next, I loaded the Select_Source table into Power Query and drilled down to its current value, which is "Division_1". I then modified the existing query for a previously uploaded table (Division_1) to reference the newly created Select_Source parameter, so that the data loaded from the query back into an Excel table named "PT_Data" would change depending on the value selected.

Now I can change the source of data for my pivot table by selecting an item from the Select_Source dropdown and clicking Refresh All on the Data tab, which is easier, I believe, than clicking the Change Data Source button on the Pivot Table Analyze tab. I found that I had to turn off Enable Background Refresh on the PT_Data query, otherwise it would be necessary to click Refresh All twice in order to update PT_Data and update the pivot table for which PT_Data is the data source.

Thank you kindly.

[dbknox]

Great video! I am attempting to pull in a table from a webpage for a common stock, and the URL of the source changes given the ticker symbol (by the user) for the stock – so my variable is in the source statement, not in the filtered steps like in your query. Is there a way to use a variable parameter in a web source?

[shirsN]

Nice video. I rarely use parameters from inside PQ. I always use it directly from excel, the way you showed for most of the part. I wonder why the most efficient way is referred as unofficial n the inefficient way as official by ms😅

[sinmaan]

Very nice. If we were to use a cell parameter to filter for text strings how would you do it? For example, we need to often filter by corporation names and we can't use the complete name we look for due to the variarions in the databases (ex: microsoft, microsoft inc, microsoft ltd, etc).

[rm]

Thank you for providing another great insight into power Query. I have 1000s of product skus and I only need to eliminate a couple dozen dynamically. Do you see a way to easily use a Power Query Parameter for accomplishing this?

[MilhouseBS]

Besides Incremental Refresh, parameters can be changed in the Scheduled Refresh for a dataset. So you could change the parameter in the service after it is published from Desktop.

[alphonsuseze]

Hello Boss, Thanks for the great video. I have a few questions/challenges!
I have situation where the records are about 1.8M records. I'm not able to load all into excel table first before adding it to the power query.
Secondly, How can I use Query to create the parameters and as well make the parameters searchable dropdown?

[capstonepm]

Thanks for the video.
Can you please guide scenario where datasource is Outlook in which I receive daily email with similar set of data against certain set of locations for each day. How can we use "Official Parameters" to selecte StartDate and EnDate so power query returns data for selected period only? Would appreciate if you can guide on this scenario or if poasible make a video on this.

[Jahspecs]

Excellent…. What about passing the Excel value to a SQL so you can limit the data table import into power query from the sql database?