Power Query vs Power BI vs Power Pivot: Differences and When to Use Each

Introduction

Power Query, Power BI, and Power Pivot are part of Microsoft’s ecosystem of tools for working with large datasets and automating workflows. They are especially useful for regular reporting tasks, and because they’re included in Microsoft Office, the skills are highly transferable. Whatever your next role, you’ll almost certainly have access to them.

Here we provide an overview of what each tool is, how they relate to one another, when to use them, and the benefits they offer - particularly for finance professionals.

In brief:

• Power Query: a tool for extracting, cleaning, and transforming data from many sources.
• Power BI: the world’s leading business intelligence and data visualisation platform.
• Power Pivot: an Excel-based data modelling tool for number-focused reporting.

Power Query

What it is

Power Query is an ETL (Extract, Transform, Load) tool accessible in Excel and Power BI. It is the starting point when working with Microsoft’s big data solutions Power BI, Power Pivot or Python in Excel.

• Extract: it can connect to and extract data from many sources including Excel files, CSVs, databases (e.g. SAP, Oracle), CRM systems (e.g. Salesforce), cloud-based applications (e.g. Google Analytics), and even scrape web pages.
• Transform: in the Power Query Editor (see Figure 1), data can be cleaned (e.g. fill gaps, correct errors) and reshaped (e.g. remove columns, change structure, add new data). Each change is recorded in the Applied Steps pane and automatically re-applied whenever the data is refreshed.
• Load: transformed data can then be loaded to: Power BI as a table, Power Pivot as a table, an Excel worksheet, directly to a PivotTable, or as a connection in Excel to then turn into a Python DataFrame.
• M code: Power Query is built on M code, a relatively complex programming language. The transformation commands available in the Ribbon of the Power Query Editor generate M code - so the user doesn’t have to – which defines how Power Query extracts and manipulates data. Advanced users are able to manually create and edit M code to further customise this process.

Figure 1: Power Query Editor with recorded transformations in Applied Steps pane (right).

When to use it

• Working with big data: if you’re looking to work with large datasets in Power BI, Power Pivot or Python in Excel then you must start with Power Query as it is the only route to bringing big data into these applications.
• Automating data cleansing and transformation: if you regularly perform the same monthly data clean-up (e.g. filling missing values, adding or removing columns), Power Query can automate the process. Once transformations are set up, clicking Refresh will extract the new month’s data, apply the steps, and load the cleaned results into your chosen application.

Benefits to Finance

• Leverage more of your organisation’s data: Power Query can connect to multiple systems that don’t normally talk to one another and bring their data - especially large datasets - into the Power Query Editor. Here, everything can be standardised, combined, and worked on together before being loaded into other applications. This ability to unify big data from disparate sources makes it possible to generate insights and value that would otherwise be out of reach.
• Automation of manual work: Power Query’s ability to record the extraction and transformation of data - and reapply these steps by clicking Refresh - can remove manual repetitive tasks, freeing up time and reducing the risk of human error.
• Source data remains unchanged: transformations are made to a copy of the data rather than the original source. This is beneficial from an audit point of view – with Applied Steps also acting as an audit trail – and removes the risk of incorrect overwrites of the data.

👉 Our Power BI Monthly Financial Reporting course and Power Query & Power Pivot: Automating Reporting course both teaches you how to extract, combine, transform and load data using Power Query, as well as diagnosing issues and adding additional months of data.

Power BI

What it is

Power BI is a data visualisation application consistently recognised by Gartner as the global leader in analytics and business intelligence platforms.

The Power BI workflow can be split into five stages:

1. Data: the built-in Power Query tool is used to extract and transform data before loading it into Power BI as tables.
2. Model: relationships are created between common fields from the tables to produce a data model (see Figure 2). This effectively maps the data so that attributes from one table can be associated with those in another.
3. DAX: Data Analysis Expressions (DAX) is the formula language of Microsoft data models. It can be added to perform powerful calculations on the tables of data. Calculated columns are created to add new data to the tables themselves. Measures are created to calculate metrics such as revenue, COGS, gross profit, and margin % to then populate visualisations.
4. Report: customisable visualisations, spread across one or more pages of a report, are populated with measures and table fields. They are dynamic and interactive: clicking on an element of a visualisation filters all the others on the page.
5. Publish: finished reports are uploaded to Power BI Service (the Cloud) and distributed across the organisation for others to view and interact with.

Figure 2: Power BI data models map data between tables via relationships. 

When to use it

• Regular reporting tasks: Power BI is ideal for regular reporting tasks where information and insights can be better conveyed through colours, shapes and sizes, rather than numbers alone (see Figure 3 & 4). Once designed and built, each step of the process from data extraction to presentation in the visualisations is automated - removing the need for manual repetitive work each reporting cycle. Ultimately, clicking Refresh in Power BI can be all that’s needed to update your reporting.
• Commercial insights analysis: Power BI’s ability to work with and perform calculations on very large datasets as well as present information visually makes it excellent for uncovering insights and quickly drilling down to the detail.
  

Figure 3: Power BI Customer Analysis report consisting of a table, matrix, donut charts, and a scatter chart. 

When not to use it

• Financial statements: a P&L is often requested in Power BI financial reporting packs, but as it is not a visualisation it does not suit Power BI’s purpose. While workarounds exist, they are complex, unintuitive, and usually result in poor-quality outputs. A better approach is to publish your Power BI reports (including the data model) to Power BI Service, then build detailed financial statements in Excel using CUBE functions connected to the published model.
• Financial modelling: Power BI is not suited to financial modelling – Excel is always a better option. Financial modelling typically involves assumptions rather than data and benefits from a flexible, transparent environment which Power BI does not offer. If, for whatever reason, processing of big data is required then Power Pivot would be a better option as results can be output to a worksheet and then worked with in a traditional spreadsheet modelling environment.
• Budgeting and Forecasting: Power BI is not suited to the creation of budgets and forecasts - traditional finance systems or Excel are more appropriate - but in some cases might be useful for visualising the end results of the process if the data is in a suitable format.
  

Figure 4: Power BI Departmental Opex Costs report viewed in a browser after publishing to Power BI Service.

Benefits to Finance

• Improved communication of information: information is often easier to interpret when presented visually through colours, shapes and sizes, rather than numbers alone. Visualised data also appeals to a wider audience, including those that are not numerical.
• Increased audience engagement: Power BI’s dynamic, visual nature is more engaging than presenting static numbers. It’s especially effective when reports are interacted with to tell a story, or when audiences can ask questions and see answers in real time.
• Fast and secure distribution: the owner retains full control of reports and data published to Power BI Service and can place varying restrictions on recipients’ access rights. The ability to share reports via links or publish to established groups of people makes distribution fast and easy.
• Automation of manual work: in addition to Power Query’s automation of data extraction and transformation mentioned above, data is mapped by the data model and calculations performed by DAX in a robust, automated manner without either requiring human intervention or maintenance, even as new data is added.
• Transition to self-service reporting: unlike traditional methods where reports are presented and followed by extra work to answer questions, Power BI lets stakeholders explore reports themselves and find answers directly. This shift to self-service saves time and effort.
  

👉 Learn how our Power BI Monthly Financial Reporting course teaches finance professionals to transform their monthly reporting duties by cleaning, modelling and visualising actuals and forecast data to create dashboards that engage their audience.

Power Pivot

What it is

Power Pivot is a data modelling add-in that exists in all versions of Excel. The article How to Add Power Pivot to Excel & Access the Data Model explains how to enable it in order for it to appear in the Ribbon and be used.

• Same technology and workflow as Power BI: although built into Excel, Power Pivot uses the same underlying technology and calculation engine as Power BI. Similar to the above Power BI steps, data is imported from Power Query (or directly from Excel), a data model is built, and DAX calculations are created. The difference lies in the outputs, explained below.
• PivotTables and PivotCharts: created and used in the same way as usual, but connected directly to the Power Pivot data model instead of an Excel worksheet range (see Figure 6). This lets them handle much larger datasets from multiple sources and display the results of DAX calculations.
• Number-focused reports with CUBE functions: Excel CUBE functions allow detailed, responsive reports such as financial statements (see Figure 7) and dashboards (see Figure 5) to be built directly on top of a Power Pivot data model. Even large-scale reports underpinned by millions of rows can update instantly via slicers. This powerful but little-known technique is a hidden gem in Excel/Power Pivot.

🎬 Watch the video at the top of the Power Query & Power Pivot: Automating Reporting page to see these reports in action.

Figure 5: Sales dashboard with slicers linked to a Power Pivot data model in Excel. 

When to use it

• Regular reporting tasks: Power BI is ideal for regular reporting tasks where information and insights can be better conveyed through colours, shapes and sizes, rather than numbers alone. Once designed and built, each step of the process from data extraction to presentation in the visualisations is automated - removing the need for manual repetitive work each reporting cycle. Ultimately, clicking Refresh in Power BI can be all that’s needed to update your reporting and even this can be automated.
• Commercial insights analysis: Power BI’s ability to work with and perform calculations on very large datasets as well as present information visually makes it excellent for uncovering insights and quickly drilling down to the detail.
  

Figure 6: PivotTable with slicers linked to a Power Pivot data model in Excel.

Benefits to Finance

• Automation of manual work: given it involves the same workflow, Power Pivot reporting benefits from the same automation of manual work mentioned in the Power BI section above.
• Lower risk mapping of data: manually consolidating datasets in Excel and mapping them with formulas carries a high risk of errors. Power Pivot reduces this risk by importing data into a data model, where mapping is more robust and does not require formula updates or maintenance.
• Convenient output options: systems such as databases often have limited options to view data or may require advanced skills such as SQL. PivotTables provide a user-friendly interface and functionality that the user is likely already very familiar with.
• Spreadsheet environment: reports in Excel are easy to customise, expand with additional calculations, and build summaries. It’s also an environment every finance professional is already familiar with.
  

Figure 7: High-level P&L with slicers linked to a Power Pivot data model in Excel.

How to Learn More

👉 Our course Power BI Monthly Financial Reporting is tailored to accounting and finance professionals and covers how to clean, model, and visualise data in a suite of financial reports to better communicate information and engage your audience.

👉 Our course Power Query & Power Pivot: Automating Reporting teaches you how to leverage more of your organisation’s data, automate workflows to save time, and build highly responsive financial statements, dashboards and PivotTables in Excel.