I’ve got the Power! A beginner’s guide to Microsoft’s Power Pivot, Power Query, 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.

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.

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.

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.

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…”

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

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.

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!