How to Split Columns in Power BI

In this Microsoft Power BI Tutorial, We will discuss how to split columns in Power BI. Here in this example, I will show how to split a single column (which is having with so much information) into multiple columns in Power BI.

I will also show you how to split columns by the number of characters in Power BI.

If you are new to Microsoft Power BI, then using this below article, you can learn the Power BI from the beginning: Getting started with Microsoft Power BI Tool

How to split a column in Power BI?

Suppose in your working environment, you have some information that has merged columns, which means a single column is having too much data. In this case, you can use this split column option to split that single column into multiple columns.

Also, learn how to export Power BI reports to excel and steps to export Power BI reports to PDF.

By taking a simple example, here I will show you how you can split the column in Power BI Desktop.

In this Power BI example, I have used a Text Employee Details table. You can follow this article to learn how to create a table by using the Power BI Enter Data option.

To split a column in the existing table, click on the Edit Queries option from the Home tab as you can see in the below screenshot.

split columns in power bi
split columns in power bi

Once you will click on the Edit Queries option, a new window will appear known as Power Query Editor. Here, when I have selected my table named Employee Details, the table is displayed as shown in below.

Also, in the below screenshot, you can see this Employee Details table has only one column and I will split this single column into multiple columns.

split column in power bi
split column in power bi

First of all, I will create a duplicate column. To make this column as duplicate, just right click on the column name and select Duplicate Column option from the menu as shown below.

how to split column in power bi
how to split column in power bi

Now you can see the duplicate column which I have created.

how to split columns in power bi
how to split columns in power bi

The second thing you have to do is Split the column. To split the columns in a table, just right click on the column which you want to split. Select the Split Column from the menu and then select the By Delimiter option.

power bi split columns
power bi split columns

When you will select By Delimiter option, then the below window will open where you have to specify some important values as:

  • Select or enter delimiter: Select or enter any delimiter from the drop-down. If your split character (which you want) is not there in the list, then select Custom option (from the drop-down list) and specify that custom character.
  • Left-most delimiter: This option will help you to split the left-most string before the first delimiter.
  • Right-most delimiter: This option will help you to split the right-most string before the first delimiter.
  • Each occurrence of the delimiter: This option will help you to split the text at each occurrence of the delimiter.
  • Split into: Select either Columns or Rows to split the data.
  • Number of columns to split into: Provide any numeric value that how many numbers of the column you wants. (As in the below screenshot, you can not see this option, because I have clicked on OK and when I have reopened this window again, it didn’t show me again)
  • Quote Character: Select any quote character from the drop-down.

Once all the values are specified, just click on OK as the below screenshot.

power bi split column
power bi split column

As I have specified the split at Left-most delimiter, that’s why you can see the Employee Name is separated from the original column and placed in the new column as shown below.

power bi split column into multiple columns
power bi split column into multiple columns

Now again I have splited this column at Each occurrence of the Delimiter and clicked on OK.

power bi split column by delimiter dax
power bi split column by delimiter dax

In the below screenshot you can see text at each occurrence of the comma is separated from the original column and placed in a new column.

power bi split column by multiple delimiter
power bi split column by multiple delimiter

Now I have tried another delimiter. For that, I have selected the following Qualification column from the table and splited that column using the Space delimiter as shown below.

power query split text into rows 1

After clicking on the OK button, you can see the text is splited by each space delimiter.

power bi split column into multiple row
power bi split column into multiple row

Split Columns By Number of Characters in Power BI

In Power BI, To split a column by number of Characters, just right-click on the column which you want to split. Click on Split Column from the context menu and select By Number Of Characters option as shown below.

power query split text into rows
power query split text into rows

Once you will select the By Number of Characters option, it will open the window which is called as Split Column by Number of Characters window. Here you have to specify some important values as:

  • Number of Characters: Provide the number of characters in which you want to split the column.
  • Once, as far left as possible: This option will help you to split the leftmost string before the number of characters.
  • Once, as far right as possible: This option will help you to split the right-most string before the number of characters.
  • Repeatedly: This option helps to split the text for every specified character that you have mentioned.
  • Split into: Select either Columns or Rows to split the data.
power query split text into columns
power query split text into columns

Now in the below screenshot, you can see the specific column is splited by a number of characters with 3 (which I have specified in above screen).

power bi split column to multiple columns
power bi split column to multiple columns

Once all things will over, just click on the Close and Apply button which is present under the Home tab. Until and unless you will not click on the Close and Apply button, then it will not effect in the Power BI Desktop.

power bi split column to multiple column
power bi split column to multiple column

Here in the below screenshot, you can see all the columns (those you are splited) inside the Employee Details table in the Power BI Desktop.

power bi split text by delimiter
power bi split text by delimiter

Also, you may like the below Power BI articles:

Hence in this Microsoft Power BI Tutorial, We discussed how to split a single column (which is having with so much information) into multiple columns with a simple example.

We also saw, how to split columns by the number of characters in Power BI.

>