238 Leveraging the M Language

Using M and DAX – differences

Generally, when you are presented with Power BI as a business intelligence tool and start exploring Power Query in it, you will use two code languages that have similar functionalities – DAX and M code – and you will probably get confused by these

similarities. In this recipe, we will discuss the main differences between these two languages and when it is better to use one instead of the other. To illustrate the main differences between them, we will create an additional column with both M code and DAX.

Getting ready

In this recipe, you need to download the FactInternetSales.csv file. In this example, we will refer to the C:\Data folder.

How to do it…

Once you open your Power BI Desktop application, you are ready to perform the following steps:

1.Click on Get Data and select the Text/CSV connector.

2.Browse to your local folder where you downloaded the FactInternetSales. csv file and open it. The following window with a preview of the data will pop up. Click on Transform Data.

3.Browse to the Add Column tab and click on Custom Column:

Figure 7.12 – Add custom column

4.Create a custom column named Gross Margin (intended here as the subtraction of TotalProductCost from SalesAmount) and enter the following formula:

=[SalesAmount]-[TotalProductCost]

You can see in the following screenshot how the subtraction is executed. Now click on OK:

Using M and DAX – differences 239

Figure 7.13 – Custom column definition

5. Change the data type of this newly created column to Decimal Number:

Figure 7.14 – Change data type to Decimal Number

240Leveraging the M Language

6.You can see a newly created column as in the following screenshot:

Figure 7.15 – Gross Margin calculated column

7.Open the Advanced Editor and observe the newly added line of code with the previous transformations:

Figure 7.16 – Advanced Editor steps

8.Browse to the Home tab and click on Close & Apply to load the data and access the Power BI interface:

Figure 7.17 – Close & Apply button

Using M and DAX – differences 241

Keep in mind that at this phase we have not imported or loaded anything in the data model yet. This custom column was created, its step was mapped in the Power Query code, and it was created as an output that comes from two inputs (SalesAmount and TotalProductCost) from the same table.

Let's see how we can create the same Gross Margin column with DAX by performing the following steps:

1. Browse to the Modeling tab and click on New column:

Figure 7.18 – Power BI view

The DAX bar will appear. Enter the following expression in the DAX bar to create a calculated column named Gross Margin 2:

Gross Margin 2 = FactInternetSales[SalesAmount]-FactInter netSales[TotalProductCost]

2.Click on the Data tab on the left section of the Power Query UI, the second one displayed in the following screenshot:

Figure 7.19 – Data tab

242 Leveraging the M Language

Browse to the last two columns, Gross Margin and Gross Margin 2, and check that you can see the same values:

Figure 7.20 – Gross Margin 2 as DAX calculated column

Gross Margin was calculated in the Power Query interface and Gross Margin 2 with DAX language inside Power BI. So, what is the difference between them if they are displaying the same values? The differences can be summed up as follows:

•Power Query: The first column was created in Power Query as a query-time transformation aimed to shape the data while extracting it from the data source. The steps are mapped, and it is considered a programming language with M IntelliSense, a code-completion aid that helps users with formula completion suggestions. You can perform steps through UI features and perform data preparation intuitively.

•DAX: The second column was created in Power BI after data was loaded as an in-memory transformation to analyze data after having extracted it. With DAX, which stands for Data Analysis Expression, it is possible to create formulas that also refer to other tables within the same data model. There is no trace of the steps performed (as in Power Query) because it is meant to be used to quickly address business challenges on top of an in-memory engine. It is a formula language,

not a programming language.