3

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?

cryocaustik
  • 439
  • 2
  • 8
  • 20

2 Answers2

3

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.

KevinSantaBarbara
  • 31
  • 2
2

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.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • While that would work on a smaller data set, it would not work when the data set grows into a few million records. When our data is filtered, it is a few hundred records, but unfiltered, it becomes unmanageable in Excel (3+ million records). We would also like to remove any information that is not related to that specific module (i.e. another user's data). – cryocaustik Oct 07 '15 at 16:19
  • Power Pivot can handle many millions of records without issue. I've made very complex models with 40M+ rows in a primary fact table that is still highly performant. That being said, if you have a need to implement security, passing a parameter to the source query is the wrong way to do that. You should either implement security in your source database or use a SSAS Tabular model instead of Power Pivot, because you can implement row-level security in that program as well. – greggyb Oct 07 '15 at 16:45
  • you are correct about the size limit, I misspoke; What I meant is that we do not want to turn a file that should be several MB into a file that is over a GB. We could debate endlessly about how there are other ways of doing somethings, but my question was if it was possible to do so with PoverPivot or PowerQuery; if not, cool, if its possible, even better! :D – cryocaustik Oct 09 '15 at 03:16
  • Then see the second link in my post - you can programmatically pass a parameter to a source query based on a cell value in the workbook. – greggyb Oct 09 '15 at 14:37
  • Thank you! And I also appreciate your suggestions on the other ways of doing it, it's just for this situation that I am having to do it a specific way. – cryocaustik Oct 14 '15 at 18:14