Use Power Query and Power Pivot Together for Better Reporting Models

By Michael Alexander

Using Power Pivot and Power Query together can help you create reporting models that are easy to manage and maintain. A reporting model provides the foundation on which the reporting mechanism is built. When you build a reporting process that imports, transforms, shapes, and aggregates data, you’re essentially building a reporting model.

Creating a poorly designed reporting model can cost you hours of manual labor spent maintaining and refreshing your reporting mechanisms. On the other hand, creating an effective model allows you to easily repeat monthly reporting processes without damaging your reports or your sanity.

The separation of data and analysis

One vital concept in a reporting model is the separation of data and analysis. The fundamental idea is that you don’t want your data to become too tied to any single way of presenting that data.

To wrap your mind around this concept, picture an invoice. When you receive an invoice, you don’t assume that the financial data on it is the true source of the data. It’s merely a presentation of data that’s stored elsewhere in a database. That data can be analyzed and presented to you in many other ways: in charts, in tables, or even on websites. This statement may sound obvious, but Excel users often fuse (or blend) data, analysis, and presentation.

For instance, some Excel workbooks contain 12 tabs, each representing a month. On each tab, data for that month is listed along with formulas, pivot tables, and summaries. Now, what happens when you’re asked to provide a summary by quarter? Do you add more formulas and tabs to consolidate the data on each of the month tabs? The fundamental problem in this scenario is that the tabs represent data values that are fused into the presentation of your analysis.

A better alternative is to create reporting models where the data and analysis layers are separated. The data layer handles the importing and transforming of raw data into staging areas. The analysis layer serves as the platform for aggregating and presenting the analysis of that raw data.

In terms of creating reporting models where you have an optimal separation of data and analysis, you can’t beat the powerful combination of Power Query and Power Pivot.

How Power Query and Power Pivot complement one another

Power Query is the perfect tool for creating the data layer. Using Power Query, you can

  • Import data from a wide array of external data sources, including SQL Server, Microsoft Access, Web Services, and even Facebook.
  • Transform and clean data before importing it into your reporting model.
  • Merge data from a variety of data sources, or append tables.
  • “Unpivot” raw data from a matrix format to a flat table.
  • Add data to Power Query’s Internal Data Model.

After you have Power Query providing the needed data layer, Power Pivot can be used to create the analysis layer. With Power Pivot, you can

  • Add relationships and set table and column properties.
  • Add custom formulas with Data Analysis Expressions (DAX).
  • Aggregate data into hierarchical views.
  • Create pivot table reports that aggregate and present data from various perspectives.

Leveraging Power Query and Power Pivot together provides a couple of benefits:

  • The data layer can be refreshed easily without the need to manually repeat transformation and data clean-up steps (Power Query does that for you). If you need data that doesn’t exist in the data layer, you can easily edit your Power Query queries to bring in new data and not disturb the analysis layer.
  • Any additional analysis can easily be created by starting additional pivot tables from the Power Query model.

In short, by using the one-two combination of Power Query and Power Pivot, you can create robust reporting models that are flexible, scalable, and easy to maintain.