Home » Power BI » How to Split Columns in Power BI

How to Split Columns in Power BI

Split column 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.

Transform data

Transform data




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

Types of Split columns in Power BI

Types of Split columns in Power BI

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.

Split columns by delimiter

Split columns by delimiter



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

Split columns into multiple columns

Split columns into multiple columns

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.
Split columns by number of characters

Split columns by number of characters



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

Split by Character

Split by 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.

Split columns by positions

Split columns by positions

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

Split columns by positions result

Split columns by positions result

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 column by lowercase to uppercase

Split column by lowercase to uppercase



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 column by uppercase to lowercase

Split column by uppercase to lowercase

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 column by digit to non digit

Split column by digit to non digit

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-

Slit column by non digit to digit

Slit column by non digit to digit

Refer more Power BI Post – Power BI

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

Leave a Reply