How to Split Columns in Power BI

In this blog you will understand all about split columns in Power BI, using this you can split column string data into multiple columns By Delimiter, By Number of characters, By Positions and many more.

Let’s get started-

Download the sample Dataset from below link-

Global_superstore_2016.xls

Step-1: Go to Home tab > Click on Transform data.

Step-2: Now right click on column which data you want to split > Split Column > It will suggest you some split options.

Split Columns By Delimiter-

Step-1: Now split Customer Name into multiple columns By Delimiter. Right click on Customer Name >Select By Delimiter.

Split Column by Delimiter window appears, it will suggest you some options-

• Select or enter delimiter: This drop-down provides you some delimiter options that you want to use as the split character like – Comma, Space, Tab, Semicolon, Equals sign. You can also use Custom delimiter.
• Left most delimiter: This option split the leftmost string before the first delimiter.
• Right most delimiter: This option split the right-most string after the last delimiter.
• Each Occurrence of the delimiter: The text split at each occurrence of a delimiter.

Step-2: Now Select Space, because we want to split customer name by Space > then Select Each occurrence of the delimiter, it will split column in every occurrence.

Step-3: As you can see in below screenshot, Customer Name has been split into multiple columns by space.

After split click on close & apply to save the changes.

Split Columns By Number of Characters-

Step-1: Right click on Customer Name column > Split Column > By Number of Characters. 

Split Column by Number of Characters window appears, it will provides you some options-

• Number of Characters: Specify the number of characters used to split the column.
• Once, as far left as possible: This option split the leftmost string before the number of characters.
• Once, as far right as possible: This option split the right-most string after the number of characters.
• Repeatedly: It will split text in every given number of characters.

Step-2: See the final result, it will split text in every 4 character.

Step-3: In same way you can use other options of Split columns by number of characters. After that click on close & apply to save the changes.

Split Columns By Positions-

Step-1: Right click on Order ID column > Split Column > By Positions.

Split Column by Positions window appears, specify the positions at which to split the text column. Positions are zero-based and comma-separated, where position zero is the start of the string.

Step-2: See the split result by positions, after that click on close & apply to save the changes.

Split columns by lowercase to uppercase

Load below sample dataset-

Example1	Example2	Example3	Example4
HElloWorld	1India	India1	HeLLoWOrld
COvid	2US	US2	cOVID
INdia	3UK	UK3	INDia

Right click on Example 4 column > Split Column > By Lowercase to Uppercase.

As you can see in below screenshot, it will split single column into multiple columns, given every instance of the last lowercase letter to the next uppercase letter.

Split columns by uppercase to lowercase

Right click on Example 1 column > Split Column > By Lowercase to Uppercase.

As you can see in below screenshot, it will split single column into multiple columns, given every instance of the last uppercase letter to the next lowercase letter.

See the result-

Split columns by digit to non-digit

It will split single column into multiple columns, given every instance of a digit followed with a non-digit.

Right click on Example 2 column > Split Column > By digit to non-digit

See the result-

Split columns by non-digit to digit

It will split single column into multiple columns, given every instance of a digit followed by a non-digit.

Right click on Example 1 column > Split Column > By non-digit to digit

See the result-

Refer more Power BI Post – Power BI

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.