Stop Wasting Time 2
This is the second post in the Stop Wasting Time series. In this post, we learn how Power Query can help us update our report in less time. After all, why would we want to spend more time than necessary updating it? That is called “wasting time” and we just don’t like wasting such a precious resource. So, let’s get to it.
As a quick recap, we started with a report like this:
It started off being manually updated with a formula like this:
But, we had to re-write all of the formulas each month as the values changed. So, we improved it with formulas that referenced the data cells, like this:
That made it better, but our formulas broke when the values moved cells. For example, when the data was sorted. Plus, if new rows were added to the data range we would need to rewrite formulas. So, we improved the workbook by storing the data in a table and using SUMIFS, like this:
And that is about as far as we’ll go using “classic” Excel items. It is time to move to some “modern” Excel tools.
The terms Classic Excel and Modern Excel were introduced to me by my Excel-friend Rob Collie. Essentially, Classic Excel represents the Excel stuff we’ve been using for a long time … formulas, functions, features, PivotTables, VLOOKUP, and so on. Modern Excel has been here for the past couple of Excel versions and include the power tools like Power Query, Power Pivot, the data model, and so on.
Now, we’ll take the next step on our journey and use Power Query. In the first post of the series, I simplified the data. As a reminder, it looked like this:
But in practice, our data is often split into multiple tables. For example, a data table that stores the values and a lookup table that stores the account names.
Here is an example of our Data table:
And here is an example of our Lookup table:
So, before we can prepare our report, we need to first combine these tables. Now, how would we tackle this with classic Excel features? Hmmm. Perhaps we could use VLOOKUP to retrieve the account names and FS Lines. And that would work just fine … but … when we write a formula, we end up “babysitting” it each month. In other words, if there are more rows than last month, we need to confirm that the formulas are filled down into the new rows. If the data source changes, we need to confirm the formulas reference the new range. So, yes, VLOOKUP would work here … but … let’s try Power Query instead and see if it offers additional advantages.
Hint: it does. At first, we’ll use Power Query to do what we could have done with VLOOKUP, but as the series continues, we’ll discover how Power Query provides benefits far beyond what VLOOKUP can do.
We’ll use the following three steps to combine our tables with Power Query:
- Get data table
- Get lookup table
- Combine them
Let’s take them one at a time.
Note: depending on your version of Excel, you may or may not have Power Query or the same commands, screens, and options as my screenshots below which were created with Excel O365 for Windows.
Get data table
To get the data table into Power Query, we select any cell in the data table and click Data > From Table/Range. The data table is loaded into Power Query as shown below:
Now, we click Home > Close & Load To … to display the dialog below:
We select Only Create Connection and click OK.
And we do the same thing for the next table.
Get lookup table
We select any cell in the lookup table and click Data > From Table/Range. We Close & Load To … and Only Create Connection.
With our data and lookup tables inside Power Query, we can now combine them.
We need to combine the data and lookup tables. In classic Excel, we’d probably use some type of lookup function, such as VLOOKUP, to do this. But here, we’ll use Power Query. We click Data > Get Data > Combine Queries > Merge. In the resulting Merge dialog, we pick our Data table from the first drop-down and our Lookup table from the second drop-down. Then, we need to identify the shared, or lookup, column in both tables. To do this, we just use the mouse to select the AcctID columns from both tables, as shown below.
In this case, the default Join Kind (Left Outer) is perfect since we want all rows from the data table, and any matching rows from the lookup table. We click OK and the Power Query Editor window is opened:
We click the Expand icon in the upper-right of the Lookup column label, and pick the columns from the Lookup table we want to see in our results. In this case, we want to see the AcctName and FS Line columns, as shown below.
We click OK … and bam:
At this point, we are looking good. We’ve combined the data and lookup tables, and are ready to return the results to a worksheet.
We click Home > Close & Load To … and decide to send the results to a Table in an Existing worksheet, as shown below:
We click OK and the results are sent into our Excel worksheet:
At this point, we can refresh this table by clicking the Data > Refresh All command. Power Query will import any changes, including new data and lookup rows, combine them, and update the results table … without us needing to babysit formulas!
For now, we’ll pull the values into our report by using SUMIFS as we did before.
But, by using Power Query to combine these tables instead of VLOOKUP, we have additional options and benefits. And, we will take our next step towards automation in the next post in the series.
We started our journey with manual steps. As we learned more, we were able to move to the right … towards our goal of automation.
We learned how tables address new data rows and the SUMIFS function addresses the sort order. In this post, we learned how Power Query can import and merge data and lookup tables without formulas. And, that going forward, we can just click the Refresh All command as table values change or new rows are added.
We’ll take another step to the right in our next post in the series 🙂
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.