I’ve got the Power! A beginner’s guide to Microsoft’s Power Pivot, Power Query, and Power BI - Catapult
Catapult brand logo
Article

I’ve got the Power! A beginner’s guide to Microsoft’s Power Pivot, Power Query, and Power BI

A Power Primer

What’s the difference between Power Query, Power Pivot and Power BI?

It seems Microsoft has been running wild recently, throwing “Power” before and “365” after  many of their products! For those new to the “Power” suite of Microsoft data collection and analysis tools, it can be difficult to understand exactly what each product does. However, an understanding of the role that each has to play is critical for companies looking to get the most out of their data.

In this blog, I’ll provide a brief introduction to Power Query, Power Pivot and Power BI. I’ll also explain why these tools are more awesome than when you pick the fastest moving line at the grocery checkout.

Power Pivot, Power Query and Power BI: What’s the difference?

What is Power Pivot?

We’ll start with Power Pivot; the engine of the entire operation. Also known as the DAX Engine (after it’s underlying language – Data Analysis Expression), Power Pivot is a baked-into excel feature that enables users to build a data model, create relationships, write calculated columns and measure formulas from a variety of sources.

In short, Power Pivot extends the capabilities of Excel by allowing for the import of larger data sets from various sources and the creation of more sophisticated data models with DAX.

From Power Pivot you can create visualizations in Excel, Power View and Power Map. Excel allows for the Pivot Tables that we all know and love, while Power Maps is a geography based visualization tool. Finally, Power View is an explorer that shows data on interactive dashboards.

Power Pivot first appeared in 2008, when you had to download a free add-on for Excel. From 2016 onward it can be found in Excel.

Power Pivot Excel

What is Power Query?

PowerQuery is a tool which enables users to retrieve, extract, and shape data prior to bringing it into Excel (and/or into the PowerPivot data model). Users can insert and remove columns, filter and sort tables, and change data types, for example.

Power Query runs on the “M”  Language (most-likely named for what it is : Mashup Query Language). Now simply labelled as “Get and Transform,” it can be found under the “Data” Ribbon in Excel.

Power Query - Get & Transform

It should be noted that Power Query is an optional tool. When importing data into Power Pivot you have two choices. You can either import it directly into Power Pivot or you can channel it first through Power Query. Your choice depends on how noisy your initial data is.

What is Power BI?

Microsoft’s Power BI is business analytics and visualization solution built on the Power Pivot and Power Query engines. Users can use “Get Data” (Power Query) and “Data Model” (Power Pivot) to shape and analyse their data. They can then create interactive dashboards and reports; visualizing their data with Power BI’s every increasing number variety of visualizations. In fact, developers can also build custom visualizations to suit the individual needs of their companies. The uber data geeks out there can even use R scripts for custom visuals.

For an example of how we’ve used Power BI in the past, check out our blog post “A Long Time Ago, On a Power BI Planet Far, Far Away…”

Using Power Pivot and Power BI like a Data Jedi

#DataJedi. We admit it. We’re Data nerds AND Star Wars nerds…

Why Does This Matter?

If you are not someone who works with data on a regular basis, you may have heard the hype around these tools and wondered, “What’s the big deal?” In their book “Power Pivot and Power BI: The Excel Users Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016,” Rob Collie and Avi Singh point to a number of reasons why data analysts and their tools are set to experience a boom in the coming years.

One is the “Data Explosion.” In short, there is data here, data there, and data everywhere. With more data being collected from a greater variety of sources, the ability to transform large amounts of data into smaller, more digestible and actionable “tidbits” will only become more sought after. The Power Query engine lets you bring in data from these sources – from normalized and clean data, such as a relational database, to unrefined data sources, like a table on a website.

Another is the fact that the value of smart, data driven decisions is increasing (as is the cost of bad ones). With the IMF projecting a mixed bag in terms of global economic growth, this becomes especially relevant. As Collie and Singh write, “When money is falling from the sky, being smart isn’t all that valuable . . . however, when the easy money stops flowing, and everyone’s margins get pressure, ‘smart’ becomes valuable once again.

Ultimate Guide: How to Future-Proof Dynamics NAV

Download this ultimate guide to learn to future-proof Microsoft Dynamics NAV.

Have Data, Need Help?

Are you generating data but lack the tools or know-how to leverage it? Or just have questions about the tools mentioned about? Don’t hesitate to leave a comment or get in touch with us. We’d love to hear from you!

Register to receive the latest Dynamics 365 Insights

Catapult reinvented consulting and implementation services for the subscription economy.

Subscription packages that include everything you need to get up and running, and transform your business

  • Icon

    Lean
    Deployment

  • Icon

    On-Demand
    Training

  • Icon

    Platform
    Upgrades

  • Icon

    Amazing
    Support

  • Icon

    Software
    Licensing

Packages from

$2,000

/mo

36-month commitment

Get Pricing

Catapult Team

Establishing a clear understanding of what you want to get out of the system is key. The rest is down to clear communication and a willingness to learn and adapt to change.

Abdullah Osman, Project Manager