Power Query vs DAX - how do you decide?

[vanessafvg]

There is some overlap between the funcationality of Power Query and DAX and I am curious as to what makes people decide to use one or the other?

[MarcelBeug]

You can refer to: https://community.powerbi.com/t5/Desktop/Dax-or-M-Language/td-p/136827

[vanessafvg]

thanks Marcel.  Great thread would be good if people who hadn't responded in that thread and want to also post their responses here.

[Eric_Zhang]

@vanessafvg

Personally, I use Power Query to model data and DAX to do calculation(especially Measures).

[michellepace]

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.

What really stuck with me is the article's comment about these knives.

Almost every knife in above picture can be used for cutting cheese except one of them! So why there are so many knives for cutting cheese?! The answer is that; these are not knives for cutting cheese! each knife is good for doing one special case. for cutting bread, bread knife gives you the best result. for cutting a fillet you normally need another type of knife. but as you agree, for some cases (such as cutting cheese!) you can use many of these knifes. 

Anyhow, read the article 🙂