In this Power BI tutorial, I will explain to you how to split a large column into small columns in Power BI.
Sometimes our column has lengthy data and our business requirement will come to split the large data and represent in the small columns. In that case, we can go for Power BI split column feature.
Now we will see how to connect the StateDetails List to Power BI desktop. Open the Power BI in the local system. Get Data -> More under Home Tab.
In the Get Data page “Online Services” -> “SharePoint Online List” -> Connect.
Pass Site URL inside the SiteURL text box in the SharePoint lists window. Click on OK.
From the Navigator Select, the SharePoint Online List name and click on Load.
Read How to use Power bi switch function
How to Split Column in Power BI?
We will see now how to split the SharePoint List “AboutState” column. In the Power BI desktop click on “Edit Queries” -> Edit Queries under the Home tab.
It will navigate Power Query Editor Page. You can see the “AboutState” column which has lengthy data. Now we are going to split the column data and display it in small columns.
Before splitting the column, it’s always a good practice to duplicating the column. To create a duplicate copy, select the column name and right-click on it. You will get some of Context menu, click on “Duplicate Column”.
Now one more copy of our AboutState column is created.
We will see now how to split the AboutState column. So for splitting operation again right-click on the Column name and click on Split Column from the Context menu. Inside the Split Column, we will get below option
- By Delimiter
- By Number of Character
- By Lowercase to Uppercase
- By Uppercase to Lowercase
- By digit to Non-Digit
- By Non-Digit to Digit
By Delimiter: We will see how to split the column by the Delimeter. Click on “By Delimiter”.
We will get Spit Column By Delimiter window. We will get 2 field
- Select or enter the delimiter
- Split at
Under Split at the radio button option, we can see “Advanced Option”. This will show some additional fields.
Select or enter the delimiter: In this dropdown field, we will get below the delimiter option. We need to select one of them as per requirement. For example, if you have selected the comma then the column will be split at each comma. The PowerBI desktop Split option also provides a Custom option, so that the user can manually enter the delimiter value.
In the Split at the radio button, we will get three radio options.
Advanced options provide you below screenshot. Split into columns: it will split your lengthy data column into multiple columns.
split into the row: It will split the column into multiple rows.
I have selected the below screen shot option. Click on OK.
We will get the below screenshot as an output.
Now we will see the “Right-most delimiter” option. Click on OK.
We will get the below output.
When we have select “Each occurrence of delimiter” then the column will be split after each occurrence.
In Advanced Option, we will see “Number of Columns to split into”field. Whatever number we will mention in the field the column will be split into that many numbers.
We have mentioned 5 number so the main column is split into 5 small columns.
By Number Of Character: The column will split based on a number of characters.
In the Split Column By Number of Character page, we have to mention the Number of characters in the “Number of Character” field.
I have mentioned 10, so after the first 10 letters, the main column will be split. We have also mentioned “Once, as far left as Possible” so from the left side after 10 letters the column will be split.
The output will look like below.
“Once, as far right as possible” this will split mentioned 10 letters from the right side.
Output screen will look like below.
“Repeatedly” option will split the column repeatedly after 10 characters. In the Number of Columns to split into the field, I have mentioned 6 so the main column will be split into 6 small columns.
The output screen will look like the below screenshot.
Split Column-> By Lowercase to Uppercase: It will split the column By Lowercase to Uppercase.
Split Column-> By Uppercase to Lowercase: It will split the column By Uppercase to Lowercase.
The output screen will look like below
Split Column -> By Digit to Non-Digit, it looks like below:
The Power BI report looks like below:
Split the Column by Non-Digit to Digit.
The Power BI report will look like below:
What we have done changes will not affect to PowerBI desktop page until you did not press the “Close and Apply” button.
Whatever the changes you made here will not effect until you hit Apply. Let me hit Close & Apply option under the Home tab.
You may like following Power BI tutorials:
- Create a Power BI Dashboard and Add Reports to Power BI Dashboard
- Add Title, Image, and Video to Power BI Dashboard
- Built-in Date Hierarchy in Power BI
- Power Bi relative date slicer
- Power BI Visualizations
- Add Web Content to Power BI Dashboard
- Power BI Custom Visuals
- Microsoft Power BI Error: This content isn’t available
- How to share Power BI Dashboard and Report
- Power BI On-premises Data Gateway
- Power BI Workbooks
- How to change data source in Power Bi
- Power BI Admin Portal
- Power BI Quick Insights
- Power BI Group By Examples
- How to Create a Power bi report from SharePoint Online list
- Power bi conditional column example
In this Power BI blog, we have discussed how to connect SharePoint List to Power BI desktop. We have also discussed how to split a column in Power BI desktop.
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”