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, and also some unique use cases. We believe in using the right tool for the job and taking advantage of the pre-built features that Power BI includes whenever possible.
The Power Query Editor is incredibly powerful even without much Power Query M knowledge. Quick Measures are the closes equivalent for DAX and will soon improve with Power BI Copilot.
Let’s look at when to use DAX vs Power Query 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, and Microsoft Analysis Services. 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 where as building it into Power Query would create a calculation during load time.
Yes, 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.
Knowing the impact of queries is only one step in the process of optimizing Power BI reports. There is an entire subfield of Power BI speed optimizations using tools 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.
Our recommendation is to learn DAX, then a basic understanding of SQL, and then focus on learning more Power Query. Using SQL statements to pre-select data from most live databases will ultimately speed up the import and data preparation process.
Will Python Replace Power BI Power Query M?
We wouldn’t be surprised if Microsoft replaced Power Query M with a Python back-end. Microsoft has been investing in a Power Query style interface to generate Python code Accelerate data prep with Data Wrangler – Microsoft Fabric along with giving Excel the ability to execute Python code.
Combine this with Microsoft’s investment in OpenAI’s ChatGPT that uses Python to execute tasks, it means that having a single programming language to train AI datasets with will mean better code generation. Secondly, the world is shifting away from SQL databases to architectures like DataBricks a Azure Synpase which execute Spark and Python code natively to improve speed. These technologies also reduce storage costs by saving files in blob storage which the Power BI Service already does.
This is pure conjecture at the moment, and it will be interesting to see how Power BI and Microsoft Fabric evolve over time.
Conclusion
There is a place for both DAX and Power Query when building Power BI reports. Power Query should be used when data transformations need to occur while data is being imported. DAX should be used when aggregating data that is calculated on the fly.
While there is a lot of overlap between the two languages. There are also instances where tools and formulas are specifically built for one language or the other.
By being aware of the different use cases and execution methods of Power Query M and DAX, developers can optimize Power BI dashboard performance to provide report viewers with the best possible experience.