Stop Wasting Time 3

We are in the middle of a series called Stop Wasting Time, where we are learning about Excel features that help us improve how we update a report. In the beginning of our journey, we were updating the report manually. Now, as we’ve learned more, we’ve been able to automate parts of it. That is, we’ve been able to delegate stuff we were doing manually to Excel. The net effect is that we’ve been able to do the same work in less time. We’ve stopped spending more time than necessary … we’ve stopped wasting time. But, we still have more improvements to make! So let’s get to it.

Background

Just to refresh our memory, let’s take a look at our report:

In the first post, we started with formulas that basically used Excel as a digital 10-key:

Since that approach forced us to rewrite formulas as the cell values changed, we upgraded the formulas to use cell references like this:

But, since that approach broke if we sorted the data table and values moved around, we upgraded our workbook to use tables and SUMIFS, like this:

In the second post, our data came in two separate tables, a data table and a lookup table. So, we learned how to use Power Query to join them (instead of VLOOKUP). Power Query produced the results table:

Now, in this post, we’ll take our next steps with Power Query.

Observation

The previous posts had an underlying assumption. We assumed the data was already in Excel. This is so subtle that Excel users often don’t even consider it. We know that in order to use SUMIFS, or Tables, or VLOOKUP, the data has to be in Excel. But, how did it get there? Probably with a manual step … like copy/paste.

We are so accustomed to this manual copy/paste step that we don’t even think about it. We just do it. Each month. Again and again. But, here’s the thing. This is a manual step that Power Query may be able to eliminate.

In this post, we’ll make the following improvements to our workflow:

• Get data with Power Query
• Aggregate in Power Query

Let’s get to it.

Get Data with Power Query

Most often, our data doesn’t originate in Excel … it is from another system or application. Of course, there are times when users manually type data into a workbook. But often, the transactions we analyze are exported from somewhere and copy/pasted into an Excel workbook. Pasting data into Excel is so common in fact, that Paste is the #1 most-used Excel command!

So, rather than copy/paste, we may be able to eliminate that step with Power Query. It depends on the location of the data, but Microsoft has done a great job of enabling Power Query to connect to all sorts of data sources, including files, databases, cloud services, and more.

In our illustration, we’ll import our data from CSV files. We’ll start with getting our data table.

Get Data Table

In Excel, we click the Data > From Text/CSV command. In the resulting dialog box, we simply navigate to the csv file and click OK and receive the following preview dialog:

If we wanted to perform any transformations (edits), we could click the Transform Data button … but since our data is clean we expand the Load button and select Load To …

We select Only Create Connection as shown below:

Now that we have imported the data table, we basically perform the same steps to retrieve our lookup table.

Get Lookup Table

We select the Data > From Text/CSV command and browse to the csv file. Once again, we Load To … and select Only Create Connection.

Now, we just need to combine the two tables.

Combine Tables

We select Data > Get Data > Combine Queries > Merge. We identify the data table in the first drop-down, the lookup table in the second drop-down, and select the AcctID columns from both tables as shown below.

We click OK and the results are displayed in the Power Query editor as shown below.

We click the expand icon in the upper-right corner of the lookup column header and pick AcctName and FS Line, as shown below.

We click OK and bam:

Now, we could send these results back to a worksheet like we did in the previous post. But, we can also do the aggregation inside of Power Query!

So, if you are keeping track, this means that Power Query can replace the copy/paste, the VLOOKUP to join the tables, and the SUMIFS to aggregate the values. Awesome … let’s do this thing!

Aggregate in Power Query

To aggregate the amount values by the FS Line column, we select the FS Line column (as shown in the screenshot above) and then click Power Query’s Transform > Group By command. The resulting dialog is shown below.

The Group by field should be FS Line so that it creates one row for each unique value in the FS Line column. Then, we want a new column called Amount that is basically the Sum of the Amount column. So we update the dialog as follows:

We click OK and bam:

We can Close & Load To … a Table in an Existing worksheet:

We click OK and the results are loaded into Excel:

At this point, we have the basic values that we need in our report. But, Power Query provides a table format … not really a report format.

So, in the next post, we’ll address this by using the data model 🙂

Next Post

• Stop Wasting Time 4

Additional Resources

• Sample data file: data.csv
• Sample lookup file: lookup.csv
• Excel file you can use to import the data and lookup files: StopWasting3.xlsx
• Read previous Power Query posts