Power Query parameters | Add flexibility to your queries | Excel Off The Grid
ฝัง
- เผยแพร่เมื่อ 15 มิ.ย. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post ★
exceloffthegrid.com/power-que...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PowerQuery
Many thanks for the clear explanation, Mark.
Thanks Ian, I'm glad it was helpful.
Thanks Mark! Great tips. Thumbs up!!
Thanks Wayne, I'm glad it was helpful 😁
Another precious brick in my PQ wall :)
Thanx Mark!
No problem. I've got more coming soon, so that wall is going to get pretty big 😁
thank you so much, you've helped me a great deal with my parameter settings!
Great to hear! 😁
Excellent. Making life more easier. Thanks Mark. 👍👍💕💕
That's my goal... making life easier for workplace warriors everywhere.😁
Literally did it today. Thanks!
Great job! 👍
Thank you - great explanation
You are welcome!
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😅
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
It’s not as straight forward as that. You need to consider the use of each parameter and then determine the best location.
I put the majority of parameters in the worksheet so that I don’t need to open Power Query at all.
Great video!
Thanks!
Good one. Thanks.
You're welcome!
awesome. very useful
I’m glad it was helpful.
Thank you for a great explanation. Can you please make a video to cover the firewall error? They are annoying!
It's on the list of topics.
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.
The formula.firewall error is not that simple. Check out this video.
th-cam.com/video/_MtuqO9Cj1E/w-d-xo.html
Privacy is not about the location of the data, but about the data which is passed across in the query request.
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.
Great news - thanks for sharing that. As it always makes me happy to know that I’ve helped somebody out. 😁
Hi, that sounds very interesting for something I'm working on. Would you mind sharing (broadly) the approach you took to be able to update 'John's table' please?
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.
Yes, that is a great use case for filtering. It is really dependent in scenario.
I’m glad your resolved the Background Refresh issue, as that can be annoying if you don’t know.
That's so cool
09:53
Thanks for the video.
Please, if you picked query in that selection, will it work the same way? I mean instead of writing the person name, you just pick a query and then it pick the names automatically.
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?
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?
Just a few questions to help narrow down the solution....
How do you know which ones to eliminate? Are there internal rules that determine the ones to eliminate? Or is is based on your knowledge?
Great
more than great
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
That's covered in this video: th-cam.com/video/umZeJHXIC3s/w-d-xo.html
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).
You can filter using the Text Contains feature. Everything else is the the same.
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.
Good point, thanks Donald.
Brilliant
Thanks Nigel 😁
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?
Yes, you can do that to. The same principles apply about replacing values in the M code.
What do you mean by passing an Excel value? Newbie 😅 question
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)?
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.
I’m pretty sure that is possible. But I’ve never had to do that myself.
If you have Office 365, it might be better to use Power Automate to save the attachments to a SharePoint or OneDrive folder. Then you use Power Query to connect to the folder.
That would be my preferred option.
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?
I figured it out from using another one of your videos! Thank you so much!
Great news - good work 👍
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?
Don’t put the records into a Table first. Use Power Query to connect to the original source. That’s what Power Query is for… connecting to different data sources.
From the loaded data you can create a separate query with the unique values, then load that separate query as a Table. Use that Table as the source for the drop down list. Then use the value in drop-down list as the parameter.
It’s all possible.
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?
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.
Whether it’s a named range or Table should make no difference.
I believe a query only updates if required in an output. A final query is loaded to a Table, Pivot Cache, or Data Model. When Refresh All happens, it updates those along with all preceding queries. Therefore a stand alone connection only query by itself may not update.
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.
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
You can load all the worksheets, by connecting to the entire workbook.
Then it’s down to your PQ skill to get out the data that you want.
Could you do the same on Power BI?
Yes, but they can't come from the current workbook as Power BI doesn't have a workbook. But they can come from any data source: External workbook, CSV,... etc.
Sort of like Slicers?
A similar concept, but Power Query and PivotTables exist in separate parts of the workflow.
Power Query is for selecting the data to include. PivotTables are for filter the data you have included.
How to between a date?
In the filter options in Power Query you can select greater than and less than options to create the same result as between.
@@ExcelOffTheGrid What am I doing wrong here?
= Table.SelectRows(#"Extracted Date", each ([targstartdate] >=StartDate,
Why would you not just use slicers?
Good question, with many answers:
1) Slicers are for report interactivity, parameters are for data preparation - so it's a different part of the workflow
2) Parameters can be used for values which are not contained in the data loaded into the table
3) For confidential information which we perform a burst report on, we don't want to load all the data into a Table, only the data related to that individual.
4) Parameters have a wider usage for meta-data and file selection which occurs before a workbook is opened.
5) Parameters can apply partial filtering (e.g. anything containing the letter "A") a slicer cannot.