Dynamic Parameter in Power Pivot Query

Question

We are using Excel 2013 and Power Pivot to build modules that consist of several Pivot tables that are all pulling data from the same Power Pivot table, which queries our T-SQL data warehouse.

In an effort to simplify and fully automate this module, we wanted to create a text field that would allow a user to enter a value (a client ID# for example), and then have that value be used as a parameter in the Power Pivot query.

Is it possible to pass a Parameter in the Power Pivot query, which is housed in a text field outside of the query?

Answer 1

Rather than pass a parameter to the data source SQL query, why not utilize a pivot table filter or slicer to do allow the users to dynamically filter the data? This is much faster than refreshing the data from the source.

If for some reason you need to pass this directly to the source query, you'll have to do some VBA work.

Answer 2

You can also pass a slicer or combobox selection to a cell. Define a name for that cell. Put that cell (and others if you have multiple text variables to use) in a table. For convenience, I usually name this table "Parameters". You can then 'read in' the parameters to your query and drop them in your query statements.

The code at the top of your query to read these parameters in might look like...

let Parameter_Table = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content], XXX_Value = Parameter_Table{1}[Value], YYY_Value = Parameter_Table{2}[Value], ZZZ_Value = Parameter_Table{3}[Value],

Followed by your query wherein instead of searching for, say a manually typed in customer called "BigDataCo", you would replace "BigDataCo" with XXX_Value.

Refreshing the link each time a different customer is selected will indeed be a very slow approach, but this has worked for me.