Pre Concatenate List of Values in Power BI using Power Query

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

You can concatenate a list of values in Power BI in two ways; Using Power BI DAX functions such as ConcatenateX, or through Power Query. For some scenarios, there is an advantage in using Power Query to do the concatenation. In this blog article, I’ll explain how this works. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Why doing concatenation in Power Query rather than DAX

Doing concatenation in DAX is definitely good and useful. However, if the concatenation is coming from a big number of items in the list, and there is no need to do the concatenation dynamically using a measure, then Power Query would work faster from the end-user point of view, the main reason is that you do the concatenation in Power Query BEFORE loading the data as a pre-calculated value. There are, of course, pro and cons of doing concatenation in each place (DAX vs. Power Query). This blog article, however, is not about where you should apply the concatenation. In this blog article, I am explaining that If you decided to do the concatenation in Power Query, how you can do it.

Sample dataset

The sample dataset I use for this example is the AdventureWorksDW2012 Excel file, which you can download it here. The two tables that I have in my datasets are DimProductCategory and DimProductSubcategory, I removed unnecessary columns, and here is the structure of tables:

DimProductCategory:

DimProductSubcategory:

List of Values

There are many different transformations that can lead to having a list of values. For this example, I used Merge transformation to create a structure like this: (merged the two tables based on ProductCategoryKey)

Every cell in the DimProductSubcategory column in the above screenshot represents a sub-table of DimProductSubcategory for a specific category of products.

The goal here is to create the list of EnglishProductSubcategoryName as a concatenated list of values, such as below:

I will show you a method that you can achieve this using Power Query. but before that, let’s see how from a sub-table, you can get only one column of it:

Get only one column from the sub-table

The sub-table of subcategories that you see in the above screenshot, has two extra columns. All we need is just a list of names. You can use Table.SelectColumns Power Query function to achieve only the columns you want. To use this function, you can add a new custom column first;

Then you can use the Table.SelectColumns function to only select the EnglishProductSubcategoryName.

One input of this function is the table column (coming from the previous step’s result, the column that has a table in every cell), and the other input is the list of columns we want as the output. This column would now produce a table in every cell, but this new table would have only one column: the EnglishProductSubcategoryName.

Convert the Table to a List

You can now convert the sub-table of subcategories to a list so that it can be then concatenated. Here is how you can do it using Add a Custom Column again and using the Table.ToList function:

Please note that I had a column name change before this step, I renamed the column from the previous step to “Single Column Product Subcategory”, which is the input I am using for the Table.ToList Power Query function.

Now I have a list in every cell;

Expand List using Extract Values

Now the final step is to expand the list. However, when you want to expand the list, there are two items: Expand to new rows, Expand to New Rows, and Extract Values.

If you use Expand to New Rows, you will get one product subcategory in each row, which is not what we want. However, if you use the Extract Values, you can choose how the concatenated values would be delimited. If you want to have a command and then space between values, then you can choose custom, and enter the characters that way;

Note that there is a space after comma character in the above screenshot which you can’t see.

Now here is the final result: (I have removed extra columns)

Here is the whole M expression of this query if you are interested:

The generated concatenated list can be used like a normal column in a table visual in Power BI:

Summary

When you have a list of values in a column and want to concatenate them together, there are two ways: Doing in DAX using functions such as ConcatenateX, or doing it in Power Query. For some scenarios, doing it in Power Query speed up the process, especially if the concatenation doesn’t need to be dynamic and can be pre-calculated. Using the steps above, you have seen how you can only select one column from a table, then convert it to list, and finally, extract values from it. There are other methods to achieve the same thing in Power Query too, which I might write about those in future blog articles.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “Pre Concatenate List of Values in Power BI using Power Query

Leave a Reply

Your email address will not be published. Required fields are marked *