How to Split Columns in Power BI

In real-time, you might get the data that has merged columns (one column with too much information). In that situation, you can use the Power BI Split Columns option to split that column into multiple columns. This article shows you how to Split Columns in Power BI with examples.

To demonstrate this Power BI split columns option, we are going to use the Text Employee table that we imported in the Enter Data article.

How to Split Columns in Power BI

To split columns in an existing table, please click the Edit Queries option under the Home tab.

Clicking the Edit Queries option opens a new window called Power Query Editor. From the screenshot below, you can see that the Power BI table has only one column.

Before we start splitting the columns in Power BI, let me duplicate this column. To do so, right-click on the column name and select the Duplicate Column option from the context menu.

Now you can see the Duplicate column. To split the columns in a Power BI table, right-click on the column that you want to split to open the context menu. Please select the Split Columns and then select the By Delimiter option.

Selecting the By Delimiter option opens the following window.

• Select or enter delimiter: From the drop-down list, please select the delimiter that you want to use as the split character. If it is not there in the list, then select the Custom option and specify that custom character.
• Left most delimiter: This option splits the leftmost string before the first delimiter.
• Right most delimiter: This option splits the right-most string after the last delimiter.
• Each Occurrence of the delimiter: The text split at each occurrence of a delimiter.

We are selecting Comma as the delimiter to Split Columns in Power BI. Remember, under the Advanced section, you can specify the Number of Columns or Rows.

Let me select the Left Most delimiter option, and click OK

Now you can see that the Employee IDs are separated from the original column and placed in the new column.

This time we select the Split at each occurrence of the delimiter option.

Now you can see, that the text at each occurrence of the comma is separated from the original column and placed in a new column.

Let me try another delimiter. For the Power BI Split Columns demonstration, we are selecting the following column and split that column using the Space delimiter.

Now you can see the different columns that store the split text at each occurrence of space.

Power BI Split Columns by Number of Characters

Right-click on the column that you want to split will open the context menu. Please select the Split Columns and then select the By Number of Characters option.

Selecting the By Number of Characters option opens the Split Column by Number of Characters window.

• Number of Characters: Please specify the number of characters used to split the column.
• Once, as far left as possible: This option splits the leftmost string before the number of characters.
• Once, as far right as possible: This option splits the right-most string after the number of characters.
• Repeatedly: The text is split for every 5 characters.

Now you can see the split columns in the Power BI query editor. Whatever changes you made (split columns ) won’t affect you until you hit Apply. Let me hit the Close & Apply option under the Home tab.

Now you can see 11 columns inside the Text Employees table.