Pre Concatenate List of Values in Power BI using Power Query

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.

Table.SelectColumns([DimProductSubcategory],{"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:

Table.ToList([Single Column Product Subcategory])

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:

let
    Source = Excel.Workbook(File.Contents("C:\Users\RezaRad\OneDrive\00 Power BI from Rookie to Rock Star - Module 1 Power BI for Data Analysts\1-Power BI Essentials\Data Sources\AdventureWorksDW2012.xlsx"), null, true),
    DimProductCategory_Sheet = Source{[Item="DimProductCategory",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(DimProductCategory_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductCategoryKey", Int64.Type}, {"ProductCategoryAlternateKey", Int64.Type}, {"EnglishProductCategoryName", type text}, {"SpanishProductCategoryName", type text}, {"FrenchProductCategoryName", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ProductCategoryAlternateKey", "SpanishProductCategoryName", "FrenchProductCategoryName"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"ProductCategoryKey"}, DimProductSubcategory, {"ProductCategoryKey"}, "DimProductSubcategory", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.SelectColumns([DimProductSubcategory],{"EnglishProductSubcategoryName"})),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Single Column Product Subcategory"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "List of Product subcategories", each Table.ToList([Single Column Product Subcategory])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"List of Product subcategories", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"DimProductSubcategory", "Single Column Product Subcategory"})
in
    #"Removed Columns1"

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.

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.