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

Brilliant

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

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

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?

Thanks Mark! Great tips. Thumbs up!!

Thank you - great explanation

Many thanks for the clear explanation, Mark.

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

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.

Why would you not just use slicers?

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?

How to between a date?

Great

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😅

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

So how do you load multiple ranges on different sheets in a workbook without first having to convert each of them into a table and load them individually? Thanks

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.

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.

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.

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?

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

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.

Could you do the same on Power BI?

Sort of like Slicers?

I use named cells in worksheets to do this, but have a lot of issues with connection only queries not updating with refresh all. Does using a table work better than a named cell? Would having the parameter in PQ read the connection only query aolve this (even though it would be redundant.)
It seems to be more of an issue because I save the file with the named cells empty but not sure how to work around that.

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

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?

Literally did it today. Thanks!

Great video!

I find many PQ tutorial on using ONE parameter. What i miss is a tutorial who uses a table with two or more columns and two or more rows as parameters and delivers one table.

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?

more than great

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

Much easier than my current method of using an Excel.CurrentWorkbook() call, in the Advanced editor: (example, get the value from the range named "rngDatabase")

dbName = Excel.CurrentWorkbook(){[Name="rngDatabase"]}[Content]{0}[Column1]

I like this solution but I need to compute the values I put in those parameter tables. When normal spreadsheet recalculation occurs, I see the correct numbers in my parameter tables. With or without clicking on the refresh button, the queries do not pick up on the calculated values. It works just the way you show if I enter a value in by hand. Do you know something other than the refresh button that will cause my parameter queries to read the computed cell values?

Hi, this guide Is great but i have this problem to add this functionality: the query that i have Is an SQL native query to my SQL server...and i would like to add the possibilty to enter the parameter from the table added in excel...but how i can reference to this value in SQL native code (that i can modify in advanced Power query editor)?

awesome. very useful

Good one. Thanks.

That's so cool

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

Gracias.