46

I have been working on Power BI for a while now and I often get confused when I browse through help topics of it. They often refer to the functions and formulas being used as DAX functions or Power Query, but I am unable to tell the difference between these two. Please guide me.

  • Sorry @teylyn. I was away from stackoverflow for a while. Thanks for your answer. –  Jun 02 '20 at 02:28
  • This is would be also helpful **[Main differences between DAX Vs M Language](https://devoworx.net/power-bi-dax-vs-m-language/)** – Mohamed May 19 '21 at 04:14

4 Answers4

84

M and DAX are two completely different languages.

M is used in Power Query (a.k.a. Get & Transform in Excel 2016) and the query tool for Power BI Desktop. Its functions and syntax are very different from Excel worksheet functions. 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.

More information about M can be found here and using your favourite search engine.

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.

There are many tutorials and learning resources for DAX if you know how to use a search engine. Or start here.

In essence: First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.

teylyn
  • 30,863
  • 3
  • 48
  • 62
15
  • M is the first step of the process, getting data into the model.

(In PowerBI,) when you right-click on a dataset and select Edit Query, you're working in M (also called Power Query). There's a tip about this in the title bar of the edit window that says Power Query Editor. (but you have to know that M and PowerQuery are essentially the same thing). Also (obviously?) when you click the get data button, this generates M code for you.

  • DAX is used in the report pane of PowerBI desktop, and predominantly used to aggregate (slice and dice) the data, add measures etc.

There is a lot of cross over between the two languages (eg you can add columns and merge tables in both) - Some discussion on when to choose which is here and here

Trubs
  • 2,285
  • 1
  • 19
  • 27
  • 1
    I don't know that I would call M and Power Query the same thing. In my opinion, Power Query is to M what Excel formulas/pivot tables/Vlookups are to VBA. You can use Power Query without every writing M 'code', but you can also only write M 'code' to achieve whatever you want in Power Query. – TylerH Jan 23 '19 at 20:41
  • 3
    @TylerH Your comparison is not really correct. When you use Power Query, you generate M code. Every time. There is no Power Query without M. The PQ Editor itself is nothing but an M code recorder and you don't have to actually **type** M code in order to generate it. In Excel you can do lots of things without VBA. – teylyn Oct 13 '19 at 21:25
  • @teylyn When I said "you can use Power Query without even writing M" I meant literally that--you don't have to manually write M. – TylerH Oct 13 '19 at 22:20
  • 1
    @TylerH You said that PQ is to M what Excel formulas/pivots are to VBA. That is wrong. You can have Excel formulas/pivots without writing or generating VBA. You cannot have PQ without M. – teylyn Oct 13 '19 at 22:46
9

Think of Power Query / M as the ETL language that will be used to format and store your physical tables in Power BI and/or Excel. Then think of DAX as the language you will use after data is queried from the source, which you will then use to calculate totals, perform analysis, and do other functions.

  • M (Power Query): Query-Time Transformations to shape the data while you are extracting it
  • DAX: In-Memory Transformations to analyze data after you've extracted it
aaronsteers
  • 1,143
  • 1
  • 9
  • 25
1

One other thing worth mentioning re performance optimisation is that you should "prune" your datatset (remove rows / remove columns) as far "upstream" - of the data processing sequence - as possible; this means such operations are better done in Power Query than DAX; some further advice from MS here: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

JohnD
  • 31
  • 7
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/26868959) – sɐunıɔןɐqɐp Aug 06 '20 at 08:42