Using M on existing queries 243
Using M on existing queries
In the previous recipes, you saw how to change and edit steps by simply opening the Advanced Editor and modifying data types without adding additional steps or changing data sources, without the need of performing steps from scratch. In this recipe, we will see additional possibilities of how to use M code on existing queries and with few steps.
Getting ready
In this recipe, you need to connect to the Azure SQL Database that you can recreate in your environment with the Adventureworks.bacpac file.
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 click on More…:
Figure 7.21 – Power BI connectors
244Leveraging the M Language
2.Browse to the Azure SQL database connector, select it, and click on Connect:
Figure 7.22 – Azure SQL database connector
3.Enter your server and database information, flag Import as Data Connectivity mode, and then click on OK:
Figure 7.23 – SQL Server database information
Using M on existing queries 245
4.Authenticate with your preferred authentication method. In this example, we're using the Microsoft account authentication:
Figure 7.24 – SQL Server database authentication
5.Select the FactInternetSales table from the database and click on
Transform Data:
Figure 7.25 – Select tables from database
246Leveraging the M Language
6.Click on Choose Columns and flag the following columns: ProductKey,
SalesTerritoryKey, TotalProductKey, SalesAmount, and OrderDate and click on OK:
Figure 7.26 – Choose Columns window
Using M on existing queries 247
7.You can see that for each step, you can see its M code in the formula bar above the data in the UI:
Figure 7.27 – Formula bar expression
8.Let's say that you want to add another column you have missed in the previous step. You could do that in the UI, but also directly with code. Click with your cursor after "Order Date" and add a comma (,), and observe how a window will pop up explaining how to interpret the M formula:
Figure 7.28 – IntelliSense example
9.After the comma, add the value "DueDate", press Enter on your keyboard and see how the column DueDate appears in the query:
Figure 7.29 – DueDate column added
248Leveraging the M Language
10.Now we want to reorder columns. We could drag and drop columns with our mouse, use the Advanced Editor only, or use a combination of both. Select the DueDate column and drag it before OrderDate:
Figure 7.30 – Drag DueDate column
11.You will again see how a step was created in the formula bar. You can now edit this step to define your own order. In this case, we will define the following order:
=Table.ReorderColumns(#"Removed Other Columns",{"SalesTerritoryKey", "ProductKey", "SalesAmount","TotalProductCost", "DueDate", "OrderDate"})
Using M on existing queries 249
Let's now create a flag column that will define whether to apply a discount to a transaction or not. The values of this new column will be Apply discount or Don't apply discount and one or the other value will refer to each row depending on the value obtained by subtracting TotalProductCost from SalesAmount. If the net sales are higher than 1000, then it will be possible to apply the discount and if not, it won't be applied. Click on Custom Column and create a new column, naming it Discount status:
Figure 7.31 – Custom Column Discount status
Enter the following code and then click on OK to create the new custom column:
if ([SalesAmount]-[TotalProductCost]) > 1000 then "Apply discount" else "Don't apply discount"