In the previous article Custom Column Vs Calculated Column, we learned if you need to create a Calculated Column in Power BI, you have to use DAX, and on the other side, if you need to create a Custom Column in Power Query Editor, you have to use M language. So in this post, we are going to explain the main differences between DAX Vs M Language by exploring the following:
What are the main differences between DAX Vs M Language?
M Language and DAX are supported by Power BI to manage, manipulate, filter, and analyze the data. but they are not the same and not dependent upon each other and each one has different syntax, structure, and logic.
What’s M Language?
- M language is a mashup query language for Power Query.
- M language stands for Data Mashup or Data Modeling.
- M language is a functional, case-sensitive language similar to F#, which can be used with Power Query in Excel, Get & Transform in Excel 2016, and Power BI Desktop.
- M language can be only used in Power Query Editor in Power BI.
- Any transformation you will do in Power Query Editor in Power BI is written in M language.
- M language can not be used in Data View or Report View in Power BI, instead, you should use the DAX.
- M language syntax is very different from Excel worksheet functions, unlike DAX.
- M language is used in Power Query Editor to preprocess and transform data before it can be loaded into the Power BI model in a more compact and optimal way.
M Language Syntax
First, you should know that the M Language is Case Sensitive language, where the Capital letter differ from the small letter.
M language has two main blocks let and in.
- let is used to define variables.
- in is used to display output.
Each line in the let section is translated to an independent step in the query settings in Power BI as shown below:
M language like any other language has comments, variables , function …etc.
Comments in M Language
In M language, you can use // to comment single line, /**/ to comment multiple lines.
Variables in M language
To define a variable in M language, you can easily type the variable name without defining data type or any additional syntax rules Ex: The Source variable below
let
Source = 1
in
SourceBut if the variable name is more than one word, so you have to define it as the following
let
#"Variable Name" = 1
in
#"Variable Name"Note: you can use special characters in the variable name in M Language
Start learning M language at the Power Query M formula language official documentation
What’s DAX?
- DAX is Data Analysis, Expression Language.
- DAX is a formula language developed by Microsoft to interact with data in a variety of platforms like Power BI, PowerPivot, and SSAS tabular models.
- DAX includes a collection of over 200 functions, operators, and constructs that can be used in a formula, or expression, to calculate and return one or more values.
- DAX helps you create new information from data already in your model.
- Using DAX, you can add three types of calculations to your data model:
- Calculated tables
- Calculated columns
- Measures
- DAX cannot be used to create calculated rows.
- DAX can not be used in Power Query Editor in Power BI. instead, you should use the M language.
- Some DAX functions are identical to Excel worksheet functions.
- DAX used to analyze data after the data is loaded into the Model.
- DAX cannot modify or insert individual values in tables.
- The below special characters are not valid in the name of objects DAX formula. Read more at DAX syntax.
.,;':/\*|?&%$!+=()[]{}<>Start Learning DAX at Data Analysis Expressions (DAX) Reference
Conclusion
In the end, M Language and DAX are supported by Power BI to manage, manipulate, filter, and analyze the data. and you should use M language in Power Query to transform Data before it loaded into the Model, and use DAX in Data View or Report view to perform in-depth data analysis after the data loaded in the memory.
