This article presents a discussion on Power query Vs DAX. As they can perform many similar tasks so it is very normal to get confused when to use which one. Except for some features in common these two are completely different in use and syntax.
Power Query performs the query from the source, through ETL process format and store the physical data tables in Excel or Power BI. DAX comes into the picture once the data is already queried from the source to calculate tables and different analysis.
Here we will briefly go through the concepts of these two with uses and example.
Power Query
It is a powerful query language and helps us build queries to mashup data. The name “M” of the M language which is actually behind the Power Query has come from “Mashup”. Power Query is available with Microsoft Excel 2016 Get & Transform and Power Query in Excel.
It is the M language syntax used in both Excel and Power BI Desktop. Mainly used for data Extraction, Transform and Load (ETL). ETL is an important step and allows us to start with our analysis tasks.
- Power Query is used in both Excel worksheets as well as Power BI models.
- It has lots of similarities in syntax with F# multi paradigm language which encompasses imperative, functional and object oriented programming methods.
- Case sensitive and contains programming blocks with “
Let"& “In“ - The user may need a little programming experience in order to create advanced data mashup queries.
Power Query is used for query-time transformations in order to shape the data while extracting it.
Uses of Power Query
As mentioned, Power Query is basically for data extraction from the source. So, if we need any kind of data transformation operation to perform, then we will do it using Power Query before loading it into Power BI.
Particularly in Power BI desktop, if we are clicking “Transform” instead of “Load” we are using Power Query or M to make a required transformation in the source data.
In the above image, we have an example of Power Query, used for calculating the length of the target text.
Suppose the source data has two columns with First Name and Last Name. And we want to concatenate them to create one single name column, then we should use M or Power Query to do that.
It can also be done using DAX but it will require several lines of codes. So better to use M for creating a new column, Pivot or Unpivot in Excel etc during ETL itself.
DAX (Data Analysis Expressions)
DAX has few functions identical to Excel but many other functions too and way powerful than Excel functions. It is used for Power Pivot, summarize, slice and dice complex data. Unlike Power Query, DAX performs In-Memory transformation to analyze the extracted data.
DAX is a common language which can be found in SQL server analysis services Tabular, in Power BI and Power Pivot in Excel.
Use of DAX
As the name suggests, DAX is typically for data analysis tasks. After the data is already extracted, it is used for data modelling and reporting. Data analysis, slice and dice can be easily done with DAX.
It is very similar to Excel functions, does not have any programming blocks like M. So, any person has experience in Excel, can easily use DAX.
While creating Power BI report, DAX helps us to calculate Year to Date, calculate aggregates, ranges and several other analytical tasks easily with built in functions.
Here is an example of DAX in the above image. It is used here to create a new column for reporting. The new column calculated the total value of another column. Here you can notice the difference between the syntax of Power Query and DAX.
Final words on Power Query Vs DAX
Power Query and DAX are different. They are built with different purposes, they have different syntax and used in different stages.
Power Query is available both in Excel and Power BI. Users with Excel and the above version has this feature. Whereas DAX is exclusively available with Power BI.
The Data Analysis eXpression language is the far more advanced version of Excel worksheet functions. Still, DAX has many similarities with Excel functions hence easy to use as almost every one of us have experience of using Excel.
Both of them are required to use the Power BI platform to its full potential. You can not chose any one of them as they are specialized for different stages of a complete analytical tasks.