Power Query (M) and DAX - What's the difference?

Introduction#

M and DAX are two building blocks of Power BI. They both are functional languages but they differ considerably and are used for very different purposes.

This page shows code samples for both languages and explains their purpose.

Remarks#

M and DAX are two building blocks of Power BI. They both are functional languages but they differ considerably and are used for very different purposes.

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 in this MSDN area: Introduction to Power Query (informally known as “M”) Formula Language

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. The Introduction to DAX is a good place to start.

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.

Power Query and DAX resources can also be found on lots of blogs (e.g. Power BI Team Blog) and in the “Desktop” section of the Power BI Community site.

Power Query (M) sample

let
    Source = Folder.Files("\\jsds1.live\dfs\Userprofiles\ixh500\UPM_Profile\desktop\PQ Desktop Demos\Set 2"),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower}}),
    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each ([Extension] = ".csv")),
    #"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
    #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TranDate", type date}, {"Account", Int64.Type}, {"Dept", Int64.Type}, {"Sum of Amount", type number}})
in
    #"Changed Type"

(Opens all files in a folder, filters on CSV files, opens and combines all CSV files and sets column data types. Note that none of that code was manually typed, but generated by clicking icons in the Power Query Editor.)

DAX example formula

Previous Quarter Sales:=CALCULATE(FactSales[Sales], PREVIOUSQUARTER(DimDate[DateKey]))

(Calculates the total sales for the previous quarter, based on the Sales column in the FactSales table and the DateKey in the DimDate table, depending on the filters applied in a PivotTable or PivotChart)