- •Contributors
- •Table of Contents
- •Preface
- •Technical requirements
- •Installing a Power BI gateway
- •Getting ready
- •How it works
- •Authentication to data sources
- •Getting ready
- •How it works
- •Main challenges that Power Query solves
- •Getting ready
- •Technical requirements
- •Getting data and connector navigation
- •Getting ready
- •Creating a query from files
- •Getting ready
- •How it works...
- •Creating a query from a folder
- •Getting ready
- •How it works...
- •Creating a query from a database
- •Getting ready
- •How it works...
- •Creating a query from a website
- •Getting ready
- •How it works...
- •Technical requirements
- •Exploring Power Query Editor
- •Getting ready
- •Managing columns
- •Getting ready
- •Using data profiling tools
- •Getting ready
- •Using Queries pane shortcuts
- •Getting ready
- •Using Query Settings pane shortcuts
- •Getting ready
- •Using Schema view and Diagram view
- •Getting ready
- •Technical requirements
- •Formatting data types
- •Getting ready
- •Using first rows as headers
- •Getting ready
- •Grouping data
- •Getting ready
- •Unpivoting and pivoting columns
- •Getting ready
- •Filling empty rows
- •Getting ready
- •Splitting columns
- •Getting ready
- •Extracting data
- •Getting ready
- •Parsing JSON or XML
- •Getting ready
- •Exploring artificial intelligence insights
- •Getting ready
- •Technical requirements
- •Merging queries
- •Getting ready
- •Joining methods
- •Getting ready
- •Appending queries
- •Getting ready
- •Combining multiple files
- •Getting ready
- •Using the Query Dependencies view
- •Getting ready
- •Technical requirements
- •Setting up parameters
- •Getting ready
- •Filtering with parameters
- •Getting ready
- •Folding queries
- •Getting ready
- •Leveraging incremental refresh and folding
- •Getting ready
- •Disabling query load
- •Getting ready
- •Technical requirements
- •Using M syntax and the Advanced Editor
- •Getting ready
- •Using M and DAX – differences
- •Getting ready
- •Using M on existing queries
- •Getting ready
- •Writing queries with M
- •Getting ready
- •Creating tables in M
- •Getting ready
- •Leveraging M – tips and tricks
- •Getting ready
- •Technical requirements
- •Adding columns from examples
- •Getting ready
- •Adding conditional columns
- •Getting ready
- •Adding custom columns
- •Getting ready
- •Invoking custom functions
- •Getting ready
- •Clustering values
- •Getting ready
- •Technical requirements
- •Using Power BI dataflows
- •Getting ready
- •Centralizing ETL with dataflows
- •Getting ready
- •Building dataflows with Power BI Premium capabilities
- •Getting ready
- •Understanding dataflow best practices
- •Getting ready
- •Technical requirements
- •Exploring diagnostics options
- •Getting ready
- •Managing a diagnostics session
- •Getting ready
- •Designing a report with diagnostics results
- •Getting ready
- •There's more…
- •Using Diagnose as a Power Query step
- •Getting ready
- •Other Books You May Enjoy
- •Index
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"