Using values from cells as parameters in Queries

[andy18]

Hi,

If you want to use excel cell values/named ranges as parameters through-out your queries, as filters, factors, parameters, etc. What is the best implementation for speed/efficiency, best practice, best way...what is better:

1) Within a query, but to be used on other queries

-  Parameter = Excel.CurrentWorkbook(){[Name="namedrangeX"]}[Content]{0}[Column1]

2) One query for each parameter
- Create query from table/range....drill down...

3) Another way not here, please let me know

Thank you

[Anonymous · Accepted solution]

Hi @andy18 ,
I understand your concern and everyone in this community is happy to help each other. You don't need to worry about an increase in views but 0 replies, because the people who view it may not be able to solve your problem, but eventually someone will reply.
Here is my take on this.
First parameter is in the query but used for other queries. This method is simple and straightforward and keeps everything in a single query making it more manageable. However, if you have a lot of parameters it can become less efficient as each query needs to reference the same named range over and over again. So, if you have a small number of parameters and want to keep your queries simple, use this method.
Next, for each parameter one query. This approach is more modular and can improve performance, especially if you have many parameters. Each parameter is managed independently, which can make debugging easier. However, managing multiple queries can become cumbersome, especially if you have many parameters. So the best practice is to use this method if you have multiple parameters and need to optimize performance. It also helps to keep queries clear and modular.
Finally, you can try using parameter tables.
You can refer to the following documentation
Building a Parameter Table for Power Query (excelguru.ca)
Create a parameter query (Power Query) - Microsoft Support
Power Query Parameters: 3 methods + 1 simple example (exceloffthegrid.com)
Create A Parameter Table For Your Power Queries
Excel Power Query Parameter from a Cell Value

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[andy18]

Thank you!!!!!!!

[foodd]

Hello @andy18 , and thank you for sharing a question, and for your follow-up reply.  

From the most important guardrail of the Community Forum:

Urgent Issues

First and foremost, the community is a "best effort" kind of support forum. There are no true guarantees or service level agreements (SLA's) regarding when you might get an answer. You may not get an answer. Don't get me wrong, the community is an incrediblely robust and an amazing resource but if you are having an urgent issue that needs addressed immediately, you need to get actual Microsoft support involved. To do this, open a support ticket here:

Power BI - https://support.powerbi.com
PowerAutomate - https://flow.microsoft.com/support
Power Apps - https://support.powerapps.com

Be Nice

The vast majority of the community are volunteers with day jobs. Being rude or complaining about Power BI, PowerAutomate, PowerApps or Microsoft will not make answerers endeavor to help you. And when the answerer misunderstands your question and gives you the wrong answer, be nice and polite and inform them that they misunderstood and re-explain what you were going for. Being terse and such will, again, not encourage answerers to endeavor to help you. I have not seen it on this site, but I have known some members in other similar communities that kept lists of other members that they would just rather not deal with.

[andy18]

Trying to understand how to change the post so the question in understood...I dont want an urgent answer, just looking at the view count increase and 0 replies makes me think I did the post wrong....

[andy18]

@Heather_iTalent Hi please let me know what is wrong with my post?, 29 views and counting and no replies...its a basic question for an MVP or experienced user.