Power Query allows you to split a column with various options, one of them is split columns by positions which split a text columns at specific positions within a given text value.
Lets see how to split a columns by positions in Power BI.
Following is the sample dataset named Data as shown below.
| DataVal |
| 1434XYZR001 |
| 2341XRTR002 |
| 5254ZDAR003 |
| 2352GYSR004 |
| 3525GSFR005 |
| 2131YFFR006 |
| 6342HGJR007 |
| 4532HFJR008 |
| 3222LJDR009 |
Now the requirement is to split the column DataVal at specific positions.
Lets say, the column value 1434XYZR001 is to be split at positions ‘0, 4, 7’ that means value will be split into three columns first column will have a value 1434, then second column will have value XYZ and third column will have a value R001.
Lets go to power query editor, in Home tab, click on Transform data.
Now Power Query Editor window opens, now click on Transform tab.
Next select column DataVal, then expand the split column dropdown, and select By Positions in context menu as shown below.
Once you click on, by positions, a split columns by positions dialog box opens.
And you can see Power BI tries to detect and give you a recommendations for those positions based on data in preview rows within the Power Query Editor.
Here you can see the positions number ‘0, 4, 8’ which are recommended by Power BI.
If recommended positions are same as that you want then you can leave it as is else you can specify the positions at which you want to split column value.
In Advance option by default split into is set to Columns that means text column is split into columns.
As you want to split columns values at positions 0, 4, and 7, so you need to specify positions.
After that click on OK button.
Once you click on OK button, you will see column value is split into columns based on specified positions ‘0,4,7’.
In case, if you want to split the text column into rows, then In Advanced options, you can select split into as rows.
Before doing this, you need to remove all pervious steps that you made to split the column.
You can remove the steps that were taken previously from Applied steps tab in Query Settings as shown below.
Once you remove the steps as shown in above screenshot, you get back old data as shown below.
Now quickly repeat the same steps as you did previously, In Power Query Editor window, click on Transform tab.
Next select column DataVal, then expand the split column dropdown, and select By Positions in context menu.
But this time, In Advanced Options, we will select Split into as Rows.
Once you click on OK button, it split the text column into Rows as shown below.
Also Read..
Group By the data using Power Query
Create Index column using Power Query
Import text using examples feature Power BI
Pivot columns – turn rows into columns
