Refreshing Referenced Queries and PowerPivot Models
Refreshing Referenced Queries and PowerPivot Models
Previous Session Summary
Lesson Synopsis
The key ingredient to this data model is the employee listing that we created in the last lesson. As you may remember, this listing was created through referencing another query (the gross salary query) and it’s vital that this employee listing query remains up to date as otherwise issues can arise in the data model. Let’s look at how this is done.
Resource Listing
The Video
- Nothing extra to add here - it's a short video!
Lesson Notes
Last lesson we built a lovely data model that will allow our Payroll team to instantly see whether their payroll reports are matching up. The next month of course, we want to be able to do the same thing, but without having to rebuild the data model.
This is straight forward enough as because we thought ahead in the last lesson and used a folder connector to connect to our payroll files and gross salary files, all our payroll team needs to do is drop the new monthly files into those connected folders each month. Hurrah.
Now the key part of this data model is the employee listing, a list of employees which we’re paying every month. This was the list we created in the last lesson referncing the Gross Salary query. This listing controls the numbers being presented in the pivot table from the other two data sources, the payroll files and gross salary files.
Chances are we’re going to get some new employees at some point in the future and we need to make sure that the list we created stays up to date. So how we do that?
How do I keep a refreshed query up to date?
Really easy – simply put some new monthly gross salary files in the Gross Salary folder and click ‘Refresh all’ in the Data tab.
PowerQuery will go to the data source, see that there are some new files in the Gross Salary folder, and extract the data for the Gross Salary query. Because we referenced the Gross Salary query to create our employee listing, the Employee Listing also gets updated. So simple!
Whatsmore, when we click ‘Refresh All’ in the Data tab, the PivotTable gets updated too!
What happens if the Employee Listing weren’t kept up to date?
If there were some employee names in the Gross Salary or Payroll files that weren’t on the employee listing, then the total sum of these employees would appear on the pivot table but would have the word (blank) on the Employee Listing. This is because PowerPivot would recognise that there is some salary for some employees in those reports that it can’t match to the Employee Listing.
Couldn’t we just use a ‘Duplicate’ command instead of a reference?
Yes you could. An Employee Listing that duplicated the Gross Salary File would also do the job and would be updated through clicking Refresh All. That said, performance of the query wouldn’t be as good because PowerQuery would need to go through all the Gross Salary steps twice (in the original query and duplicated query).
Where ‘Reference’ would shine is if in the future, payroll wanted to make a change to the Gross Salary query and that also affected the Employee Listing. An example of this might be that the Payroll team decide that they only want to reconcile salary for employee names beginning with letters A-M for example. We could filter the Gross Salary report to only show those employees and that would filter the Employee Listing accordingly.
.... and a little more!
In reality – If we were doing this exercise for real, you would also reference and create a list of employee names froom the Payroll Report files, not just the Gross Salary files. You would then append the employee name lists for the two reports together and remove any duplicates. This is to ensure that any employees that were on the payroll report but not the gross salary report, were also picked up in our listing.