Functions in Power BI forms the base for all loading and transformation steps before data is loaded into the Power BI model. These functions with M-Language may at first glance seem like a very complex and hard to understand. If you have little or no knowledge of M please visit our blog post on Power BI’s M language where we give a great introduction to the fundamental basics of M.
This blog post serves as a intro to functions and today we will focus on the basics of how Power Query uses functions to load and transform data. These functions and the M-language can serve as a catalyst for enhanced report building in Power BI.
Functions
We are going to start of by using some of the features that is readily available in the Power BI query editor in order to manipulate our data and view the results in the Query Editor. These are the functions in the top of your navigation panel:
This is a great way to get to know how Functions in Power BI works. From here we dive into ways of using M language without using the available options in the query editor.
Let’s use a example of where we loaded data into Power BI from Excel. We also transformed the data by changing our field types and renaming some of our columns. This was all done through current built in functions in the Query Editor. Let’s loo at the following two functions:
- Change column types
- Rename columns
Change column types
Loading data into Power BI often don’t have the correct column or field type for your specific data, such as date or numeric values. In our example we did the following transformations to our column types:
“#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Year”, type date}, {“Sales”, Int64.Type}, {“Region”, type text}, {“Product”, type text}}),”
Power Query can change these field types through the Data Type widget, which makes use of the Power BI M Language function called “Table.TransformColumnTypes“.
Syntax for “Table.TransformColumnTypes“:
‘Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table’
The function will return a table from your input table. Apply the transform operation to the columns specified in the parameter ‘typeTransformations (where format is { column name, type name})’. This is critical to allow column types to be in the right format for powerful transformations in the Power BI model.
Let’s look closer at our example. In the function you need to pass a table, which is our previous variable #”Promoted Headers”. For more information on variables, please visit our blog post on Power BI M language. Then you pass each column which data type needs to be changed and you specify what data type the column should be.
From our example “{“Year”, type date}” means that you pass the column Year and change the data type to Date.
Rename columns
Now we move on to rename our columns. In our example we did the following transformations to our column names:
#”Renamed Columns” = Table.RenameColumns(#”Changed Type”,{{“Sales”, “Revenue”}, {“Due Date”, “Delivery Date”}}),
Syntax for “Table.RenameColumns“:
‘Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table’
To rename a column a replacement operation renames consists of a list of two values. This is the old column name and new column name. These two names are provided in a list. In our example we change the Sales column to Revenue and the Due Date to Delivery date.
These are two simple examples to get to understand how Power BI uses functions in order to transform your data. All these steps is recorded in the Advanced Editor.
Using functions through Custom Columns
Let’s delve deeper into using functions now that we have a better understanding of how the backend of M Language work with the standard built in functions in Power Query.
We will create a new column that indicates if a sale was unprofitable, a small margin or highly profitable. Let’s use custom columns. In order to achieve this result we will use the “IF” statement in Power BI.
The Synax:
if [Revenue] < 1000 then “Unprofitable” else if [Revenue] > 1000 and [Revenue] < 2000 then “Small Margin” else “Higly Profitable”
Power Query displays this as a function as below:
This shows that there is multiple ways to use and write queries. If you are new to M-Language then the built in functions in Power Query and Custom Columns is a great way to learn and to get familiar with M-Language for more powerful transformations.
Power BI has done a lot with it’s user friendly interface and this makes it easy to both achieve results from the start, but also to learn how Power BI work’s in order to enhance your skillset and do more powerful transformations.
Writing functions from Scratch
Functions in Power BI is a great way to manipulate data. We covered the basics of functions today, but highly recommend that you spend as much time as possible to get comfortable with more functions. The best way to learn is by doing and especially aim to write most of the transformations yourself. This way will get you to quickly get the hang of the syntax and how functions work.
Check out Power BI’s guide to all the functions for M-language.
Leave A Comment