I have a data table that I connect to via SQL that has a range of transaction data that is arranged by transaction date. This data is then summarized into a Pivot table.
I would like to create a variable so that other users of the file can input an applicable date, refresh the pivot table and have power pivot refresh the table with the data for that particular date. Reason I want to do this is for other users in my company who are not as strong in excel, I would like to avoid them having to go into the data model and update the date field there.
I know in SQL you can decline a variable....just not sure if that is something that can be done in excel.
Hi Steve,
If you're using Power Query to get your data from your SQL DB and load it into Power Pivot, which you should be, then you can use a parameter in the query that links to a named range/table in the Excel workbook. See session 7 of the Power Query course for parameters.
This would filter the data you bring into Excel and the Power Pivot model to that of the date specified in the parameter, thus making the file significantly smaller than if you just brought in all of the transactions and then used the PivotTable to filter it.
Otherwise, you could put your date in an Excel Table in the workbook. Load this to Power Pivot as a Linked Table and then use that field in your measures to filter the data.
Let me know if you get stuck.
Mynda
Thanks Mynda - that is exactly what I am looking for!
I do have two follow up questions for you.
1 - For the parameter table, is there a way to use a wildcard to select all the records available for a particular field? For example for Department, if on the same table as used in the lesson I wanted to pull data for Asia Pacific and all Departments how could we do that? (Vs just pulling Asia Pacific and Finance)
2 - I had initially pulled the data from SQL direct into Power Pivot - I filtered the table to just include the date and columns I needed before I brought the data into excel. You mentioned to use Power Query to pull the data in first....is it more efficient to do it that way vs the way that I did it?
Thanks
P.S. - I really enjoy your videos, great pace, and to the point useful information 
Hi Steve,
1. if you want all departments then don't include a parameter for department. Simply remove column H from the table in my example.
2. No, I'm not aware of Power Query being more efficient at filtering than applying the filter when you get the data with Power Pivot. Both restrict the data coming into Power Pivot.
However, if you want to be able to change the date viewed in the PivotTable then with the SQL > Power Pivot method you have to bring all data in to Power Pivot and then let the PivotTable filter it. Whereas with the SQL > Power Query > Power Pivot method you can put a parameter in the workbook that allows the user to filter the data with Power Query before it's brought into Power Pivot, which will result in a smaller file.
P.S. great to hear you're enjoying the courses 🙂
Mynda
