Power Query and DAX have a lot of overlapping capabilities. We’ll look at when to choose one over the other and why Power BI has two separate programming languages that users can choose from when creating reports and dashboards.
Power Query and DAX have some overlap in capabilities but are executed differently when developing or interacting with reports. We’ll explain the nuances of each one and where you should focus your energy if you’re studying to become a Power BI developer.
Table of Contents
What is Power Query?
Power Query is a Mashup Language that’s lovingly referred to as M. It’s a designed as a data query language that’s used to extract, transform, and load (ETL) data. Power Query was created by Microsoft and is functionally and case-sensitively similar to F#.
Over the years, Power Query has evolved from being the basis of Excel PowerPivot, to becoming part of Power BI, Microsoft Analysis Services and Data Flows Gen2 in Microsoft Fabric. Queries written in Power Query can range from simple to extremely complex.
What is DAX?
Power BI DAX (Data Analysis Expressions) is a formula language specifically designed for use in Power BI, DAX allows users to perform custom calculations on models created in Power BI, Excel, and SQL Server Analysis Services (SSAS).
Unlike traditional Excel formulas, DAX is engineered to work with relational data and perform dynamic aggregation, offering a more sophisticated way to manipulate data models. It includes a library of functions, operators, and constants that can be used to build formulas, essentially driving home the point that DAX is to Power BI what VBA is to Excel.
When to Use Power Query M in Power BI
Power Query’s primary use case is data preparation and manipulation. Power Query executes data transformations at the time that data is refreshed. There are a number of use cases where Power Query would be the preferred method to transform data.
- Static After Initial Data Load – Power Query is executed when data refreshes. It does not dynamically update based on user interaction with a reporting dashboard.
- Conditional Logic – When you need to apply complex conditional logic that isn’t easily achievable through the Power Query UI.
- Row-Level Operations – For complex row-level operations that go beyond basic filtering and sorting.
- Data Type Conversions – Custom conversions between various data types, including complex transformations like string manipulations.
- Error Handling – Custom error-handling logic for missing or incorrect data.
- Pivoting and Unpivoting – For complex reshaping of tables, including multiple steps or conditional logic.
- Merging and Joining – To perform complex merges and joins between multiple tables, especially when there are custom conditions for merging.
Many of the use cases listed above have specific buttons and functions built directly into the Power BI Power Query Editor. The editor allows you to perform many functions without having to know any Power Query M coding language while giving users the flexibility to write their own code when desired.
When to Use DAX in Power BI
DAX is designed specifically for efficient data analysis and calculations in comparison to Power Query’s use for data prep and transformations. While there is some overlap with Power Query in functionality, the following use cases will highlight how DAX is most commonly used.
- Dynamic Calculations – Calculations are updated dynamically based on user interactions, filters, or slicers.
- Aggregations – DAX is designed for aggregations like summing up filtered sets of data, calculating running totals, or computing YTD totals.
- Time Intelligence – Time-based calculations like Year-over-Year growth, Month-over-Month comparisons, and other time-based aggregations.
- Contextual Analysis – DAX calculations can be performed for specific categories. For example, you can calculate averages for specific categories within a larger dataset.
- Hierarchies and Drill-Downs – DAX can be used to define custom hierarchies for drill-down exploration.
- Key Performance Indicators (KPIs) – DAX is commonly used to calculate KPIs specific to business needs.
Two of the most important takeaways is that DAX is designed to be Dynamic, perform Aggregations and Custom Calculations for Analysis.
Determining When to Use Power Query vs DAX
When there are overlapping functions in Power Query and DAX you should choose the method based on the tools that are available in Power BI and the method that will provide a better experience for report viewers.
Power Query is Applied During the Data Load Phase
The timing that Power Query executes is very important. The Power BI Service is most commonly used with a direct import data connection type. Data refreshes are scheduled in the Power BI service throughout the day, with varying frequencies.
This means that large datasets can be scheduled to refresh during off hours, and calculations that take a long time to execute can be built into Power Query and refreshed at the same time the dataset is.
The refresh may take longer, but the experience for the end user will be faster interactions with the Power BI report.
DAX Formulas are Executed in Real Time
When a user interacts with a report, such as updating a filter or slicer, DAX formulas will execute a query against the dataset. Users will have to wait longer for the Power BI service to execute the formula whereas building it into Power Query would execute the calculation prior to a report viewer opening the report.
Power Query transformations are static in the sense that they are applied during the data loading phase into the Power BI model and do not change when a user interacts with a report. Once the data is loaded into Power BI, all the transformations, cleaning, and shaping done in Power Query are “baked in,” and they won’t change until the next data refresh.
Power Query Will Increase .PBIX File Size
Transformed data is stored within the .pbix file. Power BI uses the VertiPaq engine to compress and process data transformations and calculations. Because the calculations are front loaded, the changes are stored in a file. In comparison, VertiPaq will execute DAX queries and save the instructions for queries in the .pbix file but there’s no data transformations occurring until a calculation is executed.
There is an entire subfield of Power BI speed optimizations using tools like VertiPaq Analyzer and DAX Studio. Check out the video below to learn more!
Should You Learn Power Query M or DAX First?
We recommend focusing on learning DAX over Power Query M. While both are useful to know, DAX is much more commonly used in dashboard development. The Power Query Editor in Power BI has many pre-set functions and buttons setup that will generate Power Query code behind the scenes.
Knowing how to write Power Query M will make it easier to handle fringe situations and will also come in handy when optimizing a Power BI dashboard for better performance.
DAX formulas are used every single day in dashboard development. Aggregations and time series calculations will setup aggregated values that can drilled into are the backbone of creating highly dynamic reports.
Unless your organization utilizes Power Query exclusively for data model we would focus more time on learning skills that can be more broadly applied, such as SQL or Python. Using SQL statements to pre-select data from most live databases will ultimately speed up the import and data preparation process where Python can help automate larger processes.
Power Query vs Power BI
Because Power Query is part of Power BI, the relationship between the two can be confusing. Data is imported into Power BI, but transformed and blended in Power Query. The best way to think of Power BI are two different systems combined into one. Changes made using Power Query are “Loaded” into the other side of Power BI when saved. Then the data becomes available for visualizations and writing DAX formulas.
Changes made in the Power BI report building view do not impact the underlying data in Power Query. Changes made in Power Query however, will impact the data that is available when you build report pages and DAX measures.