Split column into Column then rows and Combine Columns

[amitchandak]

Solution:

In order to perform a transformation like the one shown above, the approach is to first split the data into columns and rows, then merge them back to get the desired result.

Here’s how it can be done:

1. Beginning with (a), we’ll first have to split the column by delimiter (using space as the delimiter) into columns, at the end of which we should get something like this:

Note that after performing the operation, the data in the second column will look like 125, 247, and 689 instead of (1,2,5), (2,4,7), and (6,8,9). This is because the data type is automatically changed to a whole number from text. The expected values can be brought by deleting the ‘changed type’ step.

2. Now we have to split the comma-separated numbers into different rows which can be done by using the split column by delimiter tool. Only this time we chose comma as our delimiter and ‘Split into rows’ by selecting the Advanced options. The result should look like this:

3. The final result can be achieved by performing a simple merge on the two columns to get this:

Please find the file link and code : https://community.powerbi.com/t5/Quick-Measures-Gallery/Split-column-and-Combine-Columns/m-p/2443945...

Video link: Split column and Combine Columns: https://youtu.be/T30H_fe2uAA