Power 101 – Understanding Power Pivot, Power Query, and Power BI

Power 101 – Power Pivot, Power Query, and Power BI can be challenging to understand.  All these involve “Get” and “Transform” functions.  Here’s what they are and how they play together.

2 Different Softwares – First off, remember we are talking about two pieces of software – Excel and Power BI.

Data Models – Both software use Data Models.  Each Software was designed to easily import Data to a Data Model when limited transformation of data is needed (data doesn’t need scrubbing, is formatted well, and all fields needed for the desired analysis already live in the source).   Basic data transformation can be done in the Excel Data Model using “DAX” language.  Most Excel users can get the hang of this because they already use very similar statements in the formulas they write using expressions (think SumIf).  Basic data transformation can be done in Power BI using tools on the modeling menu (very intuitive).

Power Query – is part of Power BI and is an Add-In for Excel.  It uses “M” Language.  While it is helpful to know some “M” Language programming, Power Query has a great front-end interface that does a lot of the coding for you.  Additionally, Queries written in one software can be used in the other.  Power Query is invaluable when data sources need to be scrubbed and enhanced to ready it for the desired analysis.

The Power in all of them –    In developing these tools, Microsoft refers to “Get” and “Transform”.  The “Power” in each of these tools:

• They can “Get” data from a variety of sources (Excel, CSV, Text, ODBC and many more) and “Transform the data” (Think formatting, applying formulas, conditions etc.) so that the data can be summarized and analyzed usefully.
• They can pull multiple data sources (from different source types) into one “Data Model” which lives in the background unless you want to see detail records. When you pull data directly onto an Excel tab, there is 1,084,576 row limitation.  This sounds like a lot until you start dealing with transactions from legacy software.  The Data Model has much greater limits.  Additionally, different sources added to the table can be linked.  Usually this happens with a One-to-Many link, think Customer file (One Customer) linked to Invoice register (Many invoices with same Customer Name). 

Power Pivot – Excel has used Pivot tables for a long time.  These summarize data into a useful matrix.  Power Pivot makes data from multiple data sources available in the same Pivot Table.  Extremely useful when you want to add categories to data that don’t exist in the original data source (think Segment in QuickBooks).

Measures – a function available in both software are Measures.  Measures provide a way to easily transform data without bogging down the performance of the software.  Measures create powerful formulas to enhance analysis.

A “Catch” – Power Pivot is an Excel Add-In and is only available with select versions/subscriptions purchased – More $$$.

About the Author

Dave Creasey, owner of FRM, has a passion for helping business maximize their profitability. Dave's motto is simple: "What gets measured, gets done." And with over 30 years experience as a CFO, Controller, Big 4 Auditor and Owner, Dave has developed the tools business owners need to reach their goals.

Browse More Blogs