Power and Pivot

You’ve heard the terms “Power BI,” “Power Query” and “Power Pivot,” but maybe aren’t sure what they are. Good news! They are free tools from Microsoft and this column will talk you through them. And, while we’re at it, we’ll also talk about Pivot Tables and Pivot Charts.

Let’s zoom out for a moment for the big perspective. Excel is used for many different tasks—analyzing data, calculating journal entries and tracking tasks, among others. But, there’s one specific Excel task that’s very common: building reports. Specifically, exporting data from some system, and then summarizing it in Excel. The summarized data is a report, and we often present it using a table of numbers or a graph.

Now, imagine that Microsoft created tools that are designed to optimize and automate that process, all the way from importing the source data to preparing the final report (table or graph). And, once you’ve built the report, it’s a one-click refresh next month, and every month thereafter. In a nutshell, that’s exactly what these power tools do.

The Players

First, let’s understand where each tool fits within the overall process. Power Query retrieves and prepares data from various external sources. Power Pivot organizes multiple data tables and writes the formulas needed in our report. When we want to display a summary report in a table format, we can use a Pivot Table. When we want to display it in a graph, we can use a Pivot Chart. The data flows a bit like Figure 1.

Figure 1

The tools can be used in combination, as shown in Figure 1, but they also can be used independently. For example, perhaps you don’t need to build a graph and you just need to retrieve and clean source data; no problem, just use Power Query on its own. Here is a bit more about each.

Power Query

Power Query gets and transforms data. Specifically, it retrieves data from various sources—including CSV files, Excel files, folders, databases and more. Once connected to a data source, we can apply various transformations which help us clean and prepare the data for use. Examples of transformations include removing unnecessary columns or rows, splitting columns and unpivoting data. Once the data is cleaned and ready, it’s time for Power Pivot.

Power Pivot

Power Pivot is what allows us to manage the data model. Data model? Yes, think of it as a place to organize multiple tables and write formulas. Multiple tables? Yes. It’s important to realize that Power Query can connect to multiple data sources—all in a single Excel file, all at once.

For example, maybe we use Power Query to retrieve budget data from one place and actual data from another. We can connect to both sources in a single Excel file. We need a place to organize and relate the resulting tables, and this is where Power Pivot comes in. We can create relationships between the tables that tell Excel which columns are common. For example, the budget and actual tables may both have a common Department ID column.

Plus, our reports may require some formulas, such as the difference between budget and actual. So, we use Power Pivot to write a formula to compute the variance.

Once our tables are organized and we have our formulas, we’re ready to build our reports. Depending on the type of report, we can use a Pivot Table or Pivot Chart.

Reports: Pivot Tables and Pivot Charts

So far, we’ve connected to one or more data sources with Power Query and have used Power Pivot to organize the resulting tables and write formulas. Now we can use the data model as the source for our Pivot Tables and Pivot Charts.

Traditionally, a single Excel table or range is the source for a Pivot Table. Now, the data model is the data source for the Pivot Table and we pick and choose fields as desired. Once the reports are complete, we can save the workbook, close it and move on with our life. We don’t need to build the report all over next period because these tools are designed to make it easy to refresh the data and update the report going forward.

Power BI

But what about Power BI? In addition to making these tools available inside Excel, Microsoft also packages them and delivers them in a standalone application called Power BI. When using Power BI instead of Excel, the workflow looks more like Figure 2.

Figure 2

Power BI provides an additional option for doing this type of work. Let’s say you work at a company that has an older version of Excel that doesn’t support Power Query or Power Pivot. You can download Power BI (free at powerbi.microsoft.com) and use it instead of Excel.

Power Query and Power Pivot are critical skills for Excel users to learn. If you’d like to learn more about them, feel free to peruse the Excel University blog. And remember … Excel rules!