Stop Wasting Time 4

In this post, we are going to use the data model to improve our reporting process. This is the fourth post in the Stop Wasting Time series, and this is where everything we’ve learned comes together. By the time we have finished this post, updating our report will be extremely easy. In fact, it will only require a single click … or, no clicks if you prefer 🙂

Review

As a quick review, here is the report we are trying to automate:

In the first post, we learned how to improve our report with tables and SUMIFS. In the second post, we learned how to use Power Query to combine tables. In the third post, we learned how to use Power Query to import the data and lookup csv files, combine them without VLOOKUP, and aggregate the values without SUMIFS. Then, we loaded the combined data to a Table in an Existing worksheet with the following Import Data options:

However, in this post, we’ll load the data to the Data Model instead.

Load to Data Model

So, instead of using the options in the screenshot above, we Only Create Connection and load to the Data Model by using the options below:

At this point, the data is stored in the data model instead of being displayed in the worksheet. So, hmmm … if we can’t see the data in the worksheet, how do we access it? Well, one easy way is by building a PivotTable.

PivotTable

To build a PivotTable with data from the data model, we use the Insert > PivotTable command. In the resulting dialog, we opt to Use this workbook’s Data Model as the data source:

We click OK and now we just need to build the PivotTable.

We do so by inserting fields from the combined table (the one we merged in the previous post). We insert the FS Line field into the Rows area, and the Amount field into the Values area. We can click-and-drag or just check each checkbox.

Note: you’ll see that the name of the field in the table is “Amount” but the name in the Values area is “Sum of Amount.” When we inserted the Amount field into the report, Excel automatically created the “Sum of Amount” implicit measure (which we’ll reference shortly).

The results are shown below:

And at this point, we are super close. The only issue that remains is the structure or format of the report. Our PivotTable above looks nothing like our target report.

So … hmmm. It is like we need a PivotTable report to pull values from the data model … but we need a formula-based report to get it into the exact format we need.

A PivotTable retrieves values from the data model. That is good. But, it doesn’t provide the format that we need. But, formulas do … because we can place formulas into any cells desired, insert rows anywhere we need, and apply whatever formatting we need.

Is there somehow a way to have the best of both worlds? To somehow use formulas to retrieve values from the data model? Or better yet … is there way to just convert the PivotTable into formulas? The answers are: yes, yes, and yes!

Formulas

My Excel-friend Rob Collie taught me this trick … thanks Rob!!!!!

Since we built our PivotTable with the Data Model, we have a command available to us that will convert the PivotTable to formulas. It is called, appropriately, Convert to Formulas. To use it, we select any cell in the PivotTable, and then select PivotTable Tools > OLAP Tools > Convert to Formulas. Bam:

Note: If the PivotTable uses an ordinary range or table as the data source, the Convert to Formulas command will be disabled. 

The PivotTable is gone and in it’s place are a bunch of formulas … wow! These formulas use cube functions to retrieve values from the data model.

At this point, we are in good shape and have a couple of options. We could simply format this report as desired by inserting rows and cutting and pasting report lines into the correct order. And that would work just fine.

But, since we already have the basic report set up, let’s just modify the formulas so we can paste them into our formatted report below:

When we inspect the first amount formula, we see a CUBEVALUE function like this:

=CUBEVALUE("ThisWorkbookDataModel",$A4,B$3)

It is displayed in context below.

The first argument “ThisWorkbookDataModel” tells the function to retrieve values from the data model. That makes sense. Then, we see a reference to the row label “Accounts Payable” in A4 and the implicit measure “Sum of Amount” in B3. We can tweak these arguments so that we can paste the formula into our basic report structure.

Let’s start by replacing the reference to B$3 with this: “[Sum of Amount]” … and note the “quotes” and [square brackets] are both needed. The updated formula looks like this:

=CUBEVALUE("ThisWorkbookDataModel",$A4,"[Sum of Amount]")

Now, we just need to do the same type of thing for the row label, and enclose the label in quotes and square brackets. We can do this with the concatenation operator & as follows:

=CUBEVALUE("ThisWorkbookDataModel","["&$A4&"]","[Sum of Amount]")

Also, since we want to be able to paste the formula into any column, we’ll remove the dollar sign $ in front of the A4 reference to make it relative, as shown below:

=CUBEVALUE("ThisWorkbookDataModel","["&A4&"]","[Sum of Amount]")

With these updates made, we can now copy that formula and paste it into our report structure.

Yes … it works! And we can paste it down into the remaining cells:

And we got it!

The report looks the same as the one from the very first post. But the original version of this report was very manual to update each month. This one, which looks identical, is easier to update and maintain each month. We just need to click the Data > Refresh All command. That initiates the update sequence … Power Query retrieves any updated data, merges the data and lookup tables, aggregates the values, sends it into the data model, and our formulas retrieve the updated values.

So, our monthly update process went from manual to automated. That is how you stop wasting time my friend!

But, hang on Jeff, we still have a manual step. I mean, after you open the workbook, you still have to manually click the Refresh All button. Come on … can’t we fully automate this? Well, sure. If you don’t want to have to click the Refresh All command, you can tell Power Query to refresh when you open the workbook.

Update on open

To have the merge query refresh automatically when you open the workbook, right-click the query name in the Queries & Connections pane and select Properties.

Note: you can toggle the display of the Queries & Connection panel by clicking Data > Queries & Connections. 

In the resulting Query Properties dialog, check the Refresh data when opening the file as shown below.

Now, when you open the workbook, the query will refresh automatically.

And that is how we take a report from manual to automatic 🙂

Recap

At the end of the day, I hope that the things we covered in this series help you move to the right on the scale below:

We Excel users are all at different places on our Excel journey. Regardless of where you are now, if you’d like to move up on the scale, we offer on-demand training courses that can help.

We have two tracks: Undergraduate and Masters. The Undergraduate courses cover “classic” Excel features … things like functions, formulas, PivotTables, conditional formatting, VLOOKUP, SUMIFS, INDEX/MATCH, and so on. The Masters courses cover more technical topics, including Power Query, Power Pivot, Power BI, and VBA/Macros.

You can jump in wherever you are at without having to start at the beginning. For example, if you are already comfortable with the classic Excel features, you can jump into the Masters track.

• Course 1: the foundations
• Course 2: formulas and functions
• Course 3: PivotTables
• Course 4: internal-use workpapers and related features

View the undergraduate courses, topics, and details: Undergraduate Courses

• Course 1: Power Query, Power Pivot, Power BI, table and graph design
• Course 2: VBA and Macros

View masters courses, topics, and details: Masters Courses

Here is a visual that helps you see the progression and how the courses fit together:

If we can provide any additional information about our training courses, please contact us … we are happy to help!

And thanks for checking out the Stop Wasting Time blog series. I hope the things we discussed will help you get your work done faster!

Additional Resources

Sample file: StopWasting4.xlsx