Chapter 10: Examples of M Usage – Learn Power Query

Chapter 10: Examples of M Usage

This chapter concentrates on a few examples of M usage, including the concatenate function. We will first compare the difference between formulas in Excel and Power BI, before looking at the ampersand operator (&) and how it can be used. We will go through an example of how you can do this by using a simple name and surname concatenation formula.

We will examine how Text.From and Text.Combine can be used to join and concatenate different strings, dates, and columns. We will also learn how to set up our own SQL server legally and for free to use for non-commercial purposes; it will have full functionality. In doing this, we will also cover how to import the AdventureWorks databases into SQL to use them as a resource.

Lastly, this chapter concentrates on parameters and how they can be used effectively to filter data sources, adding parameters to control statements that allow us to filter them according to different dates. We will continue by adding parameters to order objects and columns in ascending and descending order, before looking at how we can make these changes in Power BI's Data view. Some of the skills that we will cover include defining various parameters, as well as creating and renaming them.

In this chapter, we will cover the following main topics:

  • Merging using the concatenate formula
  • Data type conversions
  • Setting up a SQL server
  • Using parameters

Technical requirements

You need an internet connection to download the relevant files from GitHub. Also, the code files for the chapter can be found at

This chapter assumes that you understand the M syntax and structure and know how to code text data types, numeric data types, lists, records, tables, searches, and shares. You should also know how to import a CSV file. If you are unsure on how to do this, please refer to Chapter 9, Working with M.

You will also need to have a SQL server. If you do not have one, I will show you how to download and install a free version of SQL Server legally. You will also need the AdventureWorks database, which can be found at

For Code in Action video of this chapter please visit the following link:

Merging using the concatenate formula

I am always amazed by how many different ways data is exported from a CSV file depending on where it comes from. With student and project management software, how the data was typed in sometimes also makes a difference. A typical way in which a person's name and surname is displayed is SMITH, John. In Excel, it is possible to take this field using the Text to Columns comma-delimited method to split the name and surname into two columns, as shown:

Figure 10.1 – Excel concatenation

You can then concatenate the two cells using the appropriate formula, and you can use the Proper function to get the correct case. Of course, this is time-consuming and there is no quick way of doing the first step automatically. The other problem is that if you are doing this with multiple different documents, it is a complete nightmare.

This first section will compare what we have just done in Excel with getting the same result in Power BI. You will soon see how much easier it is to use Power BI and the advantage of this is that once you have created this step, you can use it with different data sources, and Power BI Desktop will do this automatically for you.

So, this is what we do:

  1. Launch Power BI Desktop and select Excel from the Get Data tab.
  2. Select names.xls and click Open.
  3. Select the SurnameNames tab, and then click on Transform Data.

Depending on the version of Power BI Desktop that you have, the Transform Data tab may not be in the same place as in the following screenshot. In the latest version of Power BI Desktop (June 2020), you need to select Split Column from the Transform tab. If you have an older version of Power BI, you will need to select Data source settings from the Transform data group under the Home tab:

Figure 10.2 – The Split Column menu

Our delimiter is a comma, but if it were something different, this is where you would change it. There are a few extra options when selecting the advanced options, such as splitting it into columns or rows, but for now, we will keep the default settings and click OK.

We have now split our original column into two columns, but the surnames are in uppercase and we want them to be in title case. There are two different ways in which we can make this change

  • The first way is to write some M code: = Table.TransformColumns(#"Changed Type1",{{"Column1.1", Text.Proper, type text}}. Here, you will notice that we have asked to change the case of the first column, Column1.1, to Proper or title case. The one good thing with this is that it uses the same code as Excel—proper.
  • The second way is to right-click on the Column 1.1 heading and select Capitalize Each Word from the Transform menu, as shown:

Figure 10.3 – Capitalize Each Word

The last step is to concatenate the two fields that we have. In order to do this, we will create a new custom column and use a formula that has a [Name]&" "&[Surname] syntax. Here, we are selecting our two fields—Name and Surname—and we are combining them. The " " characters create a space between the Name and Surname fields to prevent this from becoming one string. We have to remember that we want Name first and then Surname, so when we write the code, we need to refer to Column2 and then Column1.

Click on Custom Column from the Add Column tab and type [Column1.2]&" "&[Column1.1]. As you are typing, the relevant field names will appear, and you can press the Tab key to accept them and then continue typing:

Figure 10.4 – The available columns

An alternative to this method is to merge the two columns. Select Add Column Menu  and click on Merge Columns. Choose Custom from Separator and set - as the separator. Click OK when done:

Figure 10.5 – The merge method

In the following screenshot, you will see that I have promoted the names and deleted the first row to make it look pretty, but the reality is that you would want to delete the first two columns and only have the Full Name column that you created:

Figure 10.6 – The completed concatenation

The completed file has taken less than 4 minutes to change from start to finish, and that includes if you typed in the formula yourself without using the shortcut to convert the column to title case. I personally find that this saves me so much time as I have so many different class lists that need to be converted from a CSV file that I had extracted from somewhere into a proper name and surname, which are then used in other documentation. The beauty of this as well is that it does not matter whether the usernames are in lower or uppercase as you can transform them with one of the previous steps. I personally like to create another step and create a title case version of the first names as well so that they always look the same.

There are times where you might want to do the reverse of this, where you have a name and surname and you want to concatenate them into one string so that they can be used as the login name, as a username for software, or something similar.

I am going to use the same file, but this time I will use the Names tab for my data source. You can follow all the same steps, but when you create the custom column, the formula will be slightly different. Instead of using " ", you would simply type =[Column2]&[Column1]&[Column3].

In the following screenshot, you will notice that I have a column that gives me the year that each person started working at the company, which I use in their username:

Figure 10.7 – Creating usernames

If you use this formula, you get an error as Power BI is trying to concatenate text and number data types together. This is possible to do in Power BI and will be discussed in the next section of this chapter. As I wanted to have text data types for their username, a quick way around this is to convert the number into a text data type, which then allows me to concatenate them all together without any problems.

In this section, you have seen the benefits of concatenating different fields in order to use the transformed data for other applications.

The next section deals with more complex data type conversions, which will build on your knowledge from Chapter 9, Working with M.

Data type conversions

In the previous section, we tried to concatenate a text and number data type together, which produced an error message. The same thing happens when we try to add a date or a few other things as well. In this section, we will look at the different ways in which we can join various data types.

To join two different data types, we can use the Text.From function. This function allows us to input a number, date, time, or even a binary value and it will return the numerical representation of that value. For example, if we type Text.From(7), it will give an answer of 7. This, however, is not the number 7—it is text that represents the number 7. One thing to remember is that if the value is null, then Text.From will also return null. We can use the Text.From function to convert not only numbers, but also dates and times.

There are a number of different ways in which we can concatenate data using the ampersand operator (&). In Excel, the easiest way to concatenate is to use the & symbol. If we want to concatenate the Title, Name, Street, City, Zip Code, and Country columns from the following screenshot, we could type =C2&" "&A2&CHAR(10)&D2&" "&E2&CHAR(10)&F2&" "&G2:

Figure 10.8 – Concatenating using the & symbol

I specifically have not put the columns in the correct order so that I can demonstrate that it does not matter in which order you concatenate the data. I have also used CHAR(10), which creates a line break so that it looks more structured than one long string of text.

We can also do this in Power Query and Power BI as it involves data transformation:

  1. Open the Address.xlsx document and then add the table to the Power Query editor (Select this from Table/Range in the Data tab). We will use the Text.Combine function to convert all the fields that are not text fields into text types. This includes the fields that have a generic data type and shows both the text and number data types.
  2. To add a step, we can either right-click on the applied steps and then select Insert Step After or we can click on the fx icon.

    Paste the following formula into the formula bar:

    = Table.AddColumn(#"Changed Type", "Address Labels", each Text.Combine(Record.ToList(_),"#(lf)"))

    This formula creates a new column called Address Labels and concatenates the data from the other fields from left to right. #(lf) is the Power Query line break character that is the same as CHAR(10) in Excel.

When we concatenate strings, we can either use the CONCATENATE function or we can use the % operator. They are very similar, but if we are using the CONCATENATE function, then there is a 255 string limit. The reality is that 255 characters are not going to be used, and many people prefer to use the CONCATENATE function as it is slightly easier to read. However, I personally prefer using the % operator. At the end of the day, use the method that you prefer.

We will next look at using basic operators, but you will need a SQL server. If you already have a SQL server, then you can skip the next section and load the database into your SQL server. If you do not have a SQL server, then the next section covers how to set up a legal and free SQL server on your computer.

Setting up a SQL server

In this section, we will need a few more things to run the server effectively. I realize that many individuals do not have their own SQL server, but you can download SQL Server Developer edition for free. The major difference between SQL Server Developer edition and the other editions is that although the Developer edition has almost all of the same features as the Enterprise edition, it is not for commercial use. If you want to see the SQL datasheets that compare the various versions, you can download them from

You will also need a Microsoft or an MSDN subscription, which is free, to download the relevant files. I am currently running the 2017 version, but you can download the SQL 2016, 2017, or 2019 Server Developer editions. There are a few differences among them, but overall, they are very similar. Please note that depending on the version that you download, the file size will, on average, be between 2.9–4 GB.

There are a few different ways in which you can download the Developer edition. If you have a slightly older computer, you might want to download SQL Server 2016 Developer edition, which can do everything except advanced Transact-SQL (T-SQL) querying, which we will do later in this chapter. Since advanced T-SQL querying isn't available on this version, I would not recommend using this edition unless you have an old computer.

There are two different ways to download and install the software.

The first way is to download SQL Server Developer edition from, shown as follows:

Figure 10.9 – The various SQL Server options

At this point, you can decide which version you would like to download. They can all be installed in very similar ways.

Once the file has been downloaded, there are a number of steps you will need to follow. Some steps can be skipped, but others have to be completed correctly. Let's go through them:

  1. The first step is the Planning steps (which shows you the steps you will be following. Apart from reading this, there is nothing more to do). You can skip this step and move on to the Installation step if you wish:

    Figure 10.10 – The Planning steps

  2. The Installation step is where you choose what you would like to install. Select New SQL Server stand-alone installation or add features to an existing installation and then wait for the window to pop up:

    Figure 10.11 – The installation step

    It sometimes pops up behind the current window, so you might not always see it straightaway. The easiest way is to move the current window to the left or right so that it is possible to see when another window opens.

  3. You will see the Product Key window; you need to choose Developer from the Specify a free edition drop-down list box.
  4. Once selected, click on Next and accept the I accept the license terms option before clicking on Next again.
  5. The next window is the Install Rules window, which verifies the Active Template library, the registry keys, and whether the computer is a domain controller. Do not worry if a warning from your firewall appears.
  6. The next step is Feature Selection, and this is where you can choose additional features to install:

    Figure 10.12 – The additional features

    Select Database Engine Services; the other features are not needed for this exercise, although this is the time to install any additional features if you would like to continue using them after finishing this chapter.

  7. The next step is Instance Configuration, and this is where we create and name our instances. Your screen might look different to this and not have any instances already installed:

Figure 10.13 – Instance Configuration

If you do not have any other instances, it is fine to use the default one—MSSQLSERVER. At this point, I would like to mention that while you only need one instance, this exercise looks at using parameters from two different instances, so it might be useful to create a second instance.

In Database Engine Configuration, you have the option of selecting which method you would like to use to authenticate. Personally, I like to use Mixed Mode, which allows both Server and Windows Authentication. Click Next until everything is finished and installed.

We will now look at the second way to download the software, which is from Microsoft Visual Studio Dev Essentials:

Figure 10.14 – Visual Studio Dev Essentials

You will have to agree to the terms and conditions and sign in with your Microsoft or MSDN account. You can download the Visual Studio Community tool if you like, but it is not essential. The site has changed; the SQL Server editions used to be on the first page, but they have now moved to under the Downloads section.  

After selecting the developer version that you would like, select and download the file. This will download an ISO file, which you will either need to extract or burn onto a DVD. I find extracting the ISO file easier and less time-consuming than burning it to a disk. Once it is extracted, you will need to run the setup file. You then follow exactly the same instructions as in the previous steps. It takes a few moments to complete the installation, but you then have a SQL server that you can use.

Installing SQL Server Management Studio

The next step is to connect to the SQL server using Microsoft SQL Server Management Studio (SSMS). Although there are a number of ways to connect and update databases, I find this one of the easiest. You can either click on Install SQL Server Management Tools, shown in figure 10.11, or you can open an internet browser and go to Download the Microsoft SQL Server Management Studio setup file. It is roughly around 550 MB in size, so depending on your internet speed, it should not take too long. It does take longer to install the software, so you might have to wait approximately 10 minutes:

Figure 10.15 – The SSMS installation

Once the software has finished installing, you will need to restart your computer.

Open SSMS and Connect to the server by typing in the server name of the database that you created in the previous step:

Figure 10.16 – Connecting to the SQL server

If you have successfully connected, you will see the Object Explorer panel. We need to update the database that we will use for this exercise. For this example, we will be using the Microsoft AdventureWorks database, which can be found at

Once again, you have the choice of downloading different versions, but I am using the four versions prior to and including the 2017 DW version. For this exercise, it might be worthwhile downloading more than one of the databases so that we can change the parameters to read another database, although it is only necessary to download one and then do the same steps to create the other parameters.

Download the files, which we will now use to restore the database in SSMS:

Figure 10.17 – Restoring the AdventureWorks database

Right-click on Databases and then select Restore Database…. Select Device in the Source section and click on the three ellipses (). Locate the file that you have saved and then click OK. Refer to the following screenshot:

Figure 10.18 – Adding the AdventureWorks database

This will restore the entire database. Depending on which version you downloaded, the name of the database will now be visible on the left-hand side:

Figure 10.19 – The AdventureWorks DW databases

You will notice from the preceding screenshot that I have used the same steps to restore the other versions of AdventureWorks. My one bit of advice is that if you want another user to be able to access the database, then right-click on Users in the Security folder to add another user. All our instances and databases are now set up. The next step is to connect to them via Power BI Desktop.

To connect to the SQL server from Power BI Desktop, select SQL Server from Get Data. The trick is to remember what you called your server name.

Refer to figure 10.20 to remember what you called your server. In Power BI, type in ./servername, and then click OK:

Figure 10.20 – Connected SQL

You will notice that in the preceding screenshot, I created two instances so that I can demonstrate switching between servers and databases for this exercise; but once again, if you only have the one instance, this will still work.

Now that we have the AdventureWorks database and a SQL server with instances, we can now connect to them and look at how we can use the different query parameters.

Using parameters

In this section, we will look at the Power BI Desktop query parameters, which provide a kind of way in which we can filter data. There were some developments and updates made to Power BI Desktop in 2016 that improved the ability to create parameters and use them in various ways. The most common ways that we can reference parameters are through data sources, filter rows, keep rows, and remove and replace rows. It is also possible to load the parameters into the data model so that we can reference them from measures, calculated columns, tables, and reports.

Parameterizing a data source

In this section, we will look at how we can connect different data sources that have been defined in query parameters to load different columns or connections to data sources. One of the great things is that certain pieces of software, such as Salesforce objects, SharePoint, and Power BI Desktop, allow you to use parameters when defining your connection properties. This means that you can have one parameter for the SQL server instance and another parameter for the target database.

Parameters are independent of datasets, which means that you can create the parameters before or after you have created or added your dataset. When we created a parameter in Chapter 4, Connecting to Various Data Sources Using Get & Transform, you will remember that we had to define the parameter and we set the initial values in the Power Query editor. After creating the parameters, they are listed in the Queries pane, which is where we can update and configure the parameter settings later.

Pretend that you have different customers who are using the same database structure, but they might be using different instances of SQL Server and they would typically have different database names. Using query parameters, we can switch between the different data sources and then publish reports to the different Power BI services. Let's see how to do this:

  1. Open the Power Query editor and click on Manage Parameters from the Home ribbon.
  2. In the Name textbox, type SqlSrvInstance, and then type a description of your choice into the Description textbox. The next bit of the setup might be a little bit different, depending on how many instances you have, but you will have to make sure that one of the instances that you are using is a real SQL Server name. You will need to add a .\ character before your SQL Server name:

    Figure 10.21 – Setting the parameters

  3. For Type, select Text, and then select List of values from the List of values drop-down list box. A grid will appear, and every time you press Enter, it will create another line for you to type in.
  4. Type in the SQL Server instance and make this the default value and current value.
  5. Once you click on OK, this will close the Parameters dialog box. Add this parameter to the Queries pane. You will see the current value in parentheses:

    Figure 10.22 – Instance in the Queries pane

    We are going to do exactly the same thing, but this time we are going to connect to the different databases that we have. This will then create the parameters that we need for the database that we require. Use the information from the following screenshot to create the Database parameter:

    Figure 10.23 – The AdventureWorks database's Database parameter

  6. Click on OK, and then on Close & Apply. We have now created the connection parameters and we can connect to the SQL Server instance to retrieve the database that we need.
  7. We will use T-SQL to run this query. We will need to check that the Require user approval for new native database queries property is disabled for this to work:

    Figure 10.24 – Disabling native database queries

  8. In Power BI Desktop, select Options and Settings from the File menu, and then click on Security.
  9. Back in Power Bi Desktop, select SQL Server Database from the Get Data tab, before clicking on Connect.
  10. Choose SqlSrvInstance from the Server drop-down list box and choose the Database option for the Database parameter:

    Figure 10.25 – The Server parameter

  11. Click on the Advanced options arrow and paste the following T-SQL statement into the SQL statement box:

    SELECT h.SalesPersonID AS RepID,

    CONCAT(p.LastName, ', ', p.FirstName) AS FullName,

    CAST(SUM(h.SubTotal) AS INT) AS SalesAmounts

    FROM Sales.SalesOrderHeader h INNER JOIN Person.Person p

    ON h.SalesPersonID = p.BusinessEntityID

    WHERE h.SalesPersonID IS NOT NULL

    AND YEAR(h.OrderDate) = 2012

    GROUP BY h.SalesPersonID, p.FirstName, p.LastName

    ORDER BY FullName ASC;

  12. Click OK, and if everything is working correctly, you should get a preview that looks similar to the following:

Figure 10.26 – Preview of the script

If you look at the top of the preview, you will notice our two parameters—SqlSrvInstance and Database. So, this means everything is connected to the correct default parameters.

Click on Load, and this will load to the dataset. Before continuing, you might want to rename this to something more appropriate by either right-clicking on the Fields panel on the right or by clicking on the ellipses () on the right-hand side and then renaming it:

Figure 10.27 – The completed source parameter

The beauty behind this is that we have created two different parameters that we already set up for different SQL Server instances, as well as different databases. We can use the same parameters for different datasets, which means you can use the same connection information every time you have a dataset that uses the same data source.

If we look at the M statement that was generated when we used the T-SQL statement earlier, notice how easily the parameters have been referenced:

= Sql.Database(SqlSrvInstance, Database,

Although we have created two parameters, we still have Filtered Row in APPLIED STEPS in the preceding scenario. In the next section, we are going to look at how we can convert a date into a parameter.

Adding parameters to filter data

We can add in additional parameters so that it is possible to filter according to more than one thing. Part of the code that we used earlier is AND YEAR(h.OrderDate) = 2012#. What we can do is replace 2012 with a parameter, which will allow us to change the year with a new parameter. Go through the same steps as before (under Parameterizing a data source), but this time, create a parameter called YearSales and use a range of 2011 to 2014 for the list of values:

Figure 10.28 – The completed source parameter

Now that we have created the new parameter, we will need to change 2012 to " & YearSales % " (with the quotation marks) in the M code:

Figure 10.29 – Changing the year M code to a parameter

It is possible to see how well the new parameter works by selecting a different year and then going back to the RepSales parameter and viewing the sample. You will notice that each time you change the year, the figures are different. You might also notice that when you select the 2015 year, there is no data for this and the preview is blank:

Figure 10.30 – Selecting different years

It is possible to expand on this and do more than just change the text. We can use parameters and change mathematical formulas as well, which we will look at in the next section.

Adding parameters to control statement logic

Up until now, all of the parameters that we have created change text from something to something else. It is possible to make the text do additional things, such as carry out mathematical equations. For example, we can change SUM to AVG, and this will then carry out a different equation. Although it is possible to use these in just about any mathematical equation, I am going to demonstrate SUM, AVG, MIN, and MAX

Create another parameter and call it MathsAgg with the values of SUM(h.SubTotal), AVG(h.SubTotal), MAX(h.SubTotal), and MIN(h.SubTotal):

Figure 10.31 – The completed MathsAgg parameter

In the database, there might be various columns with figures that we could apply this to. For example, there might be a DiscountAmounts column and we could then use the MIN(h.DiscountAmounts) formula to work out the minimum amount for this column. We can have different parameters for each column, but only if the different datasets can support this.

We will once again need to update the M statement by replacing SUM(h.SubTotal) with " & MathsAgg & " (including the quotation marks):

Figure 10.32 – The completed MathsAgg M code

Once again, if your dataset allows for this, you could use the same parameter that you created here and apply it to a different parameter that you created before—for example, the DiscountAmounts column that we mentioned earlier.

We have now used different mathematical aggregate functions and used them in a parameter, but in the next section, we will look at how we can use other built-in functions to create parameters to order things.

Adding parameters to order objects

We can order columns in Excel and Power BI using either the ascending or descending formula. We can use functions such as ordering on parameters to order various columns. With this, it is possible to use various columns as our values, which means that we can not only choose whether we would like something to be in ascending or descending order, but we can also have the different column names to choose from.

As we did earlier in the preceding section, we will create another parameter and call this one OrderResults. We will create four different values in our list—FullName ASC, FullName DESC, SalesAmounts ASC, and SalesAmounts DESC:

Figure 10.33 – The OrderResults parameter

Once the parameters have been created, change the M statement by changing FullName ASC to " & OrderRes & " (including the quotation marks):

Figure 10.34 – The completed OrderResults parameter

In this chapter, we used parameters to filter different scenarios. In earlier chapters, we predominately used APPLIED STEPS to filter the data that we required. One of the problems with APPLIED STEPS is that everything is set in stone, and once you have the steps in order, it is not always that easy to change things around. Parameters, however, allow you to change multiple things without APPLIED STEPS as you are adding a type of filter, which is applied to the datasets and is then applied.

The one bit of advice that I do have at this point is that when you are creating your templates, every time you create a new parameter, you should apply and save the changes in a different filename. For example, in this section, we have created five different types of parameters, and I have named my file Chapter10.Parameter1, Chapter10.Parameter2, and so one. This was done for two reasons:

  • I have a completed file for each parameter that I could use as an example.
  • If I broke the M code at any time, I could always revert to the previous file.

The one drawback to using parameters is that up until this point, we have opened the Power BI Query Editor to change the parameters that we needed, which is time-consuming, but we can do this directly in the Data view. Let's look at that next.

Using parameters in the Data view

From the Data view, it is possible to change all of the parameters that we have created without having to go into the Query Editor, which makes things quicker and easier.

Click on Edit Parameters under Transform data in the Home ribbon, which will bring up the Enter Parameters window:

Figure 10.35 – The parameters from Data

The window itself is self-explanatory, and you can use the drop-down list boxes next to each parameter to choose the relevant filters that you would like. Once you are finished, click on OK, which will automatically filter according to the parameters that you have chosen. It is also possible to change the parameters while you are in Report view, which changes your visualizations automatically.

Once your parameters are set up, using the Parameter window makes it much quicker to filter your requirements without having to open the Power Query Editor.


Looking back at this chapter, we started off by looking at the differences between the Excel and Power BI languages and how concatenation is similar but uses different languages. The ampersand operator (&) was used to show how we can join different strings, dates, and objects before examining how this was different, but similar, to using Text.From and Text.Combine.

The setting up of a SQL server is a bit technical and normally, a technician would set this up in a medium to large corporation, although this is changing in the current climate with the use of scalable online platforms that allow you to purchase only what you require, which makes this more affordable for smaller organizations. My advice when installing is to make sure you concentrate on the different steps, as if you change a setting such as the authentication, you might not be able to access your databases.

Lastly, we investigated parameters. We took an intensive look at setting up parameters and accessing different data sources, as well as using parameters to filter data from previous years, using parameters to order columns in ascending and descending order. In the final part of this chapter, we looked at how we can set all of the parameter settings in Power BI's Data view without having to go into Query editor.

The next chapter concentrates on custom functions and delves into the custom functions of M.