Thanks Mark! Great tips. Thumbs up!!

@ExcelOffTheGrid

Жыл бұрын

Thanks Wayne, I'm glad it was helpful 😁

Many thanks for the clear explanation, Mark.

@ExcelOffTheGrid

Жыл бұрын

Thanks Ian, I'm glad it was helpful.

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

@ExcelOffTheGrid

Жыл бұрын

That's my goal... making life easier for workplace warriors everywhere.😁

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

@ExcelOffTheGrid

Жыл бұрын

No problem. I've got more coming soon, so that wall is going to get pretty big 😁

Literally did it today. Thanks!

@ExcelOffTheGrid

11 ай бұрын

Great job! 👍

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

@ExcelOffTheGrid

6 ай бұрын

Great to hear! 😁

Thank you - great explanation

@ExcelOffTheGrid

Жыл бұрын

You are welcome!

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

@ExcelOffTheGrid

Жыл бұрын

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.

That's so cool

Great video!

@ExcelOffTheGrid

9 ай бұрын

Thanks!

Great

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

@ExcelOffTheGrid

Жыл бұрын

It's on the list of topics.

more than great

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.

@ExcelOffTheGrid

Жыл бұрын

Great news - thanks for sharing that. As it always makes me happy to know that I’ve helped somebody out. 😁

@mohamedbennour9920

4 ай бұрын

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?

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.

@ExcelOffTheGrid

Жыл бұрын

The formula.firewall error is not that simple. Check out this video. kzread.info/dash/bejne/kYGo19OIaabQYag.html Privacy is not about the location of the data, but about the data which is passed across in the query request.

Good one. Thanks.

@ExcelOffTheGrid

2 ай бұрын

You're welcome!

awesome. very useful

@ExcelOffTheGrid

2 ай бұрын

I’m glad it was helpful.

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😅

Brilliant

@ExcelOffTheGrid

Жыл бұрын

Thanks Nigel 😁

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.

@ExcelOffTheGrid

Жыл бұрын

Good point, thanks Donald.

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.

@ExcelOffTheGrid

Жыл бұрын

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.

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?

@ExcelOffTheGrid

Жыл бұрын

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?

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

@ExcelOffTheGrid

11 ай бұрын

That's covered in this video: kzread.info/dash/bejne/p6GOx6yBiKypY9Y.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).

@ExcelOffTheGrid

Жыл бұрын

You can filter using the Text Contains feature. Everything else is the the same.

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.

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?

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?

@ExcelOffTheGrid

Жыл бұрын

Yes, you can do that to. The same principles apply about replacing values in the M code.

@txreal2

Жыл бұрын

What do you mean by passing an Excel value? Newbie 😅 question

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.

@ExcelOffTheGrid

Жыл бұрын

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.

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?

@ExcelOffTheGrid

Жыл бұрын

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.

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?

@dbknox

11 ай бұрын

I figured it out from using another one of your videos! Thank you so much!

@ExcelOffTheGrid

11 ай бұрын

Great news - good work 👍

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

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.

@ExcelOffTheGrid

11 ай бұрын

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.

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

@ExcelOffTheGrid

Жыл бұрын

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?

@ExcelOffTheGrid

Жыл бұрын

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?

@ExcelOffTheGrid

Жыл бұрын

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?

@ExcelOffTheGrid

Жыл бұрын

In the filter options in Power Query you can select greater than and less than options to create the same result as between.

@DanielJones-rw1lw

Жыл бұрын

@@ExcelOffTheGrid What am I doing wrong here? = Table.SelectRows(#"Extracted Date", each ([targstartdate] >=StartDate,

Why would you not just use slicers?

@ExcelOffTheGrid

Жыл бұрын

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.