264 Leveraging the M Language
Every time that you refresh your data, you will have an updated Date table that can be used for further purposes. This is an example of how you can leverage some Date
expressions in M language, applying intuitive logic and getting the most out of this tool.
Leveraging M – tips and tricks
Using M code and editing existing queries from the Advanced Editor implies paying attention to some general rules in order to avoid common errors. In this recipe, we will discover some tips and tricks to keep in mind when editing queries and using M code on them.
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.Rename the ProductKey column to ProductKeyCode:
Figure 7.54 – Rename column
Leveraging M – tips and tricks 265
4.Click on Choose Columns and flag ProductKeyCode, OrderQuantity, SalesAmount, and OrderDate:
Figure 7.55 – Choose columns
266Leveraging the M Language
5.Now open the Advanced Editor and, at line 5, edit the renamed value, and instead of ProductKeyCode write ProductKey_Code:
Figure 7.56 – Edit column name in Advanced Editor
After renaming the column, click on Done. You can see that No syntax errors were detected and everything seems to be OK.
6.You won't see any data, but you will incur an error that looks like the one in the following screenshot:
Figure 7.57 – Error displayed
This error refers to the fact that the Choose Columns step expects a column called
ProductKeyCode, but at Step 6 we renamed it ProductKey_Code without changing it in the step that followed.
7.Once again, open the Advanced Editor. In line 6, rename the ProductKeyCode column to ProductKey_Code and click on Done:
Figure 7.58 – Update column name in the Removed Other Columns step
Leveraging M – tips and tricks 267
8. You will now see that the data is displayed correctly:
Figure 7.59 – Corrected query output
This example suggests that you have to pay attention when you edit existing steps and be aware of the potential for errors when you modify the names in the steps that follow. Also, pay attention to separate the different steps with a comma (,).
8
Adding Value to Your Data
You have the chance to connect to your data and create and transform it as you want thanks to a wide range of options explored in the previous chapters. Moreover, Power Query offers the chance to add data and enrich it with additional columns or define some functions to retrieve data. By adding columns, you can define your own customized logic in a few steps and leverage the UI or M code expressions, which represents the language behind the scenes of Power Query. M code expressions can be used to build functions and define input values and programmatically retrieve a defined output in order to simplify the entire transformation process.
In this chapter, you will explore how you can add data as new columns based on a pattern or a logic of existing data enriching it with valuable information and using a set of transformations.
In this chapter, we will cover the following recipes:
•Adding columns from examples
•Adding conditional columns
•Adding custom columns
•Invoking custom functions
•Clustering values