Split columns by positions

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

Merge Queries

Import text using examples feature Power BI

Pivot columns – turn rows into columns

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 

 356 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.