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.
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.
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.
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.
Now you can see the duplicate column which I have created.
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.
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.
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.
Now again I have splited this column at Each occurrence of the Delimiter and clicked on OK.
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.
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.
After clicking on the OK button, you can see the text is splited by each space delimiter.
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.
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.
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).
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.
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.
Also, you may like the below Power BI articles:
- Power BI Visualizations
- Power BI Custom Visuals
- Add Title, Image, and Video to Power BI Dashboard
- Create a Power BI Dashboard and Add Reports to Power BI Dashboard
- PowerBI Report Filter using Slicer Visualization
- Power BI: Create a various report from SharePoint Online list
- Access to the resource is forbidden error in Power BI
- Create a Power BI report from Excel using Power BI Desktop
- Add Web Content to Power BI Dashboard
- [Video Tutorial] Create Power BI Dashboard step by step tutorial
- Microsoft Power BI Error: This content isn’t available
- Power BI On-premises Data Gateway
- Power BI Buttons
- Power BI Workbooks
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.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”