Power Query Model to Data Model

[curiouspbix0]

Power Query seems to be more poweful for creating a robust model for complex requirements dealing with mulitple sources with varying joins.

When would one use Power Query vs DAX for modelling ? 

[v-diye-msft · Accepted solution]

Hi @curiouspbix0 

What is the difference between DAX and Power Query (aka 'M')?, this article helped me: https://radacad.com/m-or-dax-that-is-the-question

The article also speaks about when to use Dax and when to use Power Query:

Which one is best?

Quick answer is Depends! Depends on type of usage. If you want to create a concatenated column; Power Query (M) is better option in my view, because that is normally like the ETL part of your BI solution, you can simply build your model and data sets in a way you like it to be. But if you want to create something like Year To Date; Obviously you can do that in Power Query or M, but it will be lots of code, and you have to consider many combinations of possibilities to create a correct result, while in DAX you can simply create that with usage of TotalYTD function. So the answer is; there is no best language between these two. The type of usage identifies which one is best. Normally any changes to prepare the data for the model is best to be done in M, and any analysis calculation on top of the model is best to be done in DAX.

M is a mashup query language used to query a multitude of data sources. It contains commands to transform data and can return the results of the query and transformations to either an Excel table or the Excel or Power BI data model. DAX stands for Data Analysis expressions. DAX is the formula language used in Power Pivot and Power BI Desktop. DAX uses functions to work on data that is stored in tables. Some DAX functions are identical to Excel worksheet functions, but DAX has many more functions to summarize, slice and dice complex data scenarios. They have different functions, for a same calculated column using DAX, maybe it needs several steps to get the result using M language. Sometimes you need to created a calculated column using several functions in DAX, while it's easier and more quick. So the performance of DAX and Power Query are uncompariable based on my understanding, it depends on the requirements. They have their own advantages. Please review the following articles.

The article:The VertiPaq Engine in DAX  describes the how DAX works.
The difference between DAX and M: Dax or M Language

[amitchandak]

@curiouspbix0 , Both have there own place. Data preparation should be done in M/power query.

Refer These

https://radacad.com/m-or-dax-that-is-the-question

https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

[Mariusz]

Hello @curiouspbix0

Typically, all data preparation must be done in Power Query, and then you can use DAX for measures.

Best regards
Mariusz

If this post helps,then please consider accepting it as the solution.

Please feel free to connect with me.
Linkedin