Split delimeted text to multiple rows in Power Query

In this blog let's take a look at how to split multiple columns with concatenated strings into rows in PowerQuery, we will write M code ourself because if you do the same operation from UI you will end up creating a cross join between different list columns after splitting strings to list.

You can generate the input table by using the following PQ code

Input table:

Our goal is to convet the last 3 columns into rows so for ID = 1 and Name = Charlie we want 3 rows because the maximum elements amongst all 3 columns 'Metro', 'NoFrills', and 'WalMart' are 3, if one of these columns has 4 elements like in case of ID = 2 then we need to expand this row into 4 new rows but also keeping in mind that it should not produce a CROSSJOIN.

Final table will look like the following image:

The first thing that we want to do is to get the names of the columns that will be transformed, I have hard coded them into a list but you can you List.LastN & Table.ColumnNames as well to get the last N column names.

Next we will transform these columns into lists and as you can see these columns have items in the format of ["A", "B", "C"] so we can clean this string and then use Expression.Evaluate to evaluate our custom expression.

The above code iterates the list of column names and produces a list of 3 elements for each column - { column name (x), a function - (each try....), the data type of the tranformed element (type list)}

Inside the code I have first removed the square brackets and then concatenated the curly brackets which signify the list object in M language and then once I have a string such as "{ A, B, C}" I can transform it into a list object of 3 elements with Expression.Evaluate and in case if that fails for example when we don't have any items in the list then we use try otherwise to catch errors and replace them with an empty list denoted by {null}.

This steps returns the following structure:

In the next by using Table.TransformColumns we can apply the previous list which will transform those 3 columns:

Now that we have 3 columns of list we can use Table.Combine that takes list columns and combines them into a Table object.

The above code combine the list columns into the following table:

As you can see we have produced the table correctly without any crossjoin and the empty cells are treated as nulls. Perfect!

Next we only need to expand the column "x" with the following code or the UI if you prefer that.

Finally to change the data type to text for the new column you can use the following code:

Which returns this table

Full M code:

There is another way of converting that object ["A", "B", "C"] to an M list object by using Json.Document, and then you can just use the remaing part of the full code as it is.

Thanks to u/MonkeyNin at r/PowerBI for showing this trick!

Here is another variation that uses the same logic but this it just combines all the columns together and we also use the default transformation feature of Table.TransformColumns where you can apply a particular transformation to all the columns.

If you want you can do the above transformation in Python as well, following is the input data as a Python dict:

Python code to get the same table: