In Power Query transformations, the split column by delimiter has something that you need to be aware of. This transformation is great and extremely helpful, but beware. This involves splitting the column by delimiter. The delimiter is the line feed character in this case. The question is, how many columns will the column be split into? It will find the maximum number needed and “hard-code” that number of columns into the transformation. Let’s use an example to see what that means.

Here is a very simple example. To put a line feed character into a cell to force a new row, use Alt+Enter. Below is a screenshot.

Above is our Excel table. Click inside the table and go to the Data menu. Choose From Table/Range.

Split by Rows

Instead of the default (columns), we can split by rows.

In Power Query go to Transform, Group By, ProductId and Description.

If we add another color to the original table, and refresh the green table, it updates correctly to 4 for the first product. It did not update to 4 when we split by columns because we only split by 3 columns. We could of course manually change it to 4 columns and it would have worked, but we don’t want to rely on manually changing it in case we get a product that comes in more than 3 or 4 colors.

R Language

To split a column in a data frame in R, use the separate function.