In real-time you might get the data that has merged columns (one column with too much information). In that situation, you can use Power BI Split Columns option to split that column into multiple columns.
This article will show you, How to Split Columns in Power BI with example.
How to Split Columns in Power BI
To demonstrate this Power BI split columns option, we are going to use the Text Employee table that we imported in Enter Data article.
In order to split columns in an existing table, please click Edit Queries option under the Home tab.
Clicking Edit Queries option will open a new window called Power Query Editor. From the below screenshot you can see that the table has only one column
Before we start splitting the columns, let me duplicate this column. To do so, right-click on the column name, and select Duplicate Column option from the context menu.
Now you can see the Duplicate column
In order to split the columns in a table, right-click on the column that you want to split will open the context menu. Please select the Split Columns and then select By Delimiter option.
Selecting the By Delimiter option will open the following window.
- Select or enter delimiter: Please select the delimiter that you want to use as the split character from the drop down list. If it is not there in the list, then select Custom option and specify that custom character.
- Left most delimiter: This option will split the left most string before first delimiter.
- Right most delimiter: This option will split right most string after the last delimiter.
- Each Occurrence of the delimiter: Text will split at each occurrence of a delimiter.
We are selecting Comma as the delimiter. Remember, under the Advanced section you can specify the Number of Column or Rows
Let me select the Left Most delimiter option, and click OK
Now you can see that the Employee ID’s are separated from the original column and placed in the new column.
This time we will select the Split at each occurrence of the delimiter option
Now you can see, 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 demonstration, we are selecting the following column, and split that column using the Space delimiter.
Now you can see the different columns that stored the split text at each occurrence of space.
Split Columns by Number of Characters in Power BI
Right-click on the column that you want to split will open the context menu. Please select the Split Columns and then select By Number of Characters option.
Selecting the By Number of Characters option will open 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 will split the left most string before number of characters.
- Once, as far right as possible: This option will split right most string after the number of characters.
- Repeatedly: Text will split for every 5 characters.
Now you can see the split columns in Power BI query editor
Whatever the changes you made here will not effect until you hit Apply. Let me hit Close & Apply option under the Home tab.
Now you can see 11 columns inside the Text Employees table
