How to split column in Power bi

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.

Here, we will see how to split the SharePoint online list column in Power BI desktop. Before that, we will how to get the SharePoint Online list data in PowerBI desktop?

I have created a SharePoint list of StateDetails and store lengthy data. Now our aim is to split the column into small columns in Power BI desktop.

power bi split column
how to connect powerbi to sharepoint list

Connect SharePoint Online List to PowerBI Desktop

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.

power bi split column by delimiter
how to connect powerbi to sharepoint list

In the Get Data page “Online Services” -> “SharePoint Online List” -> Connect.

power bi split column by delimiter into rows
connect powerbi to sharepoint

Pass Site URL inside the SiteURL text box in the SharePoint lists window. Click on OK.

power bi split column by delimiter dax
connect powerbi to sharepoint list

From the Navigator Select, the SharePoint Online List name and click on Load.

power bi split column into multiple columns
connect powerbi to sharepoint

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 Home tab.

power bi split column
power bi split column

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.

power bi split column into multiple columns
power bi split column into multiple 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”.

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

Now one more copy of our AboutState column is created.

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

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”.

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

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.

power bi split column into rows
power bi how to split column

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.

power bi split column by position
how to split columns in power bi

In the Split at the radio button, we will get three radio options.

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

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.

split column in power bi desktop
how to split columns in power bi

I have selected the below screen shot option. Click on OK.

split columns in power bi using dax
connect powerbi to sharepoint list

We will get the below screenshot as an output.

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

Now we will see the “Right-most delimiter” option. Click on OK.

power bi split column
power bi split column

We will get the below output.

split column data in power bi
connect powerbi to sharepoint list

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.

split column by delimiter in power bi
connect powerbi to sharepoint list

We have mentioned 5 number so the main column is split into 5 small columns.

split column dax power bi
connect powerbi to sharepoint list

By Number Of Character: The column will split based on a number of characters.

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

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.

power bi split column by delimiter
how to split columns in power bi

The output will look like below.

power bi split column by delimiter into rows
power bi split column into multiple columns

“Once, as far right as possible” this will split mentioned 10 letters from the right side.

power bi split column by delimiter dax
power bi split the column into multiple columns

Output screen will look like below.

power bi split column into multiple columns
how to split columns in power bi

“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.

power bi split column
power bi split column

The output screen will look like the below screenshot.

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

Split Column-> By Lowercase to Uppercase: It will split the column By Lowercase to Uppercase.

power bi split column into multiple columns
power bi split column into multiple columns
power bi split column by position
power bi split column into multiple columns

Split Column-> By Uppercase to Lowercase: It will split the column By Uppercase to Lowercase.

split column in power bi
power bi split column

The output screen will look like below

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

Split Column -> By Digit to Non-Digit, it looks like below:

split column in power bi desktop
how to split columns in power bi

The Power BI report looks like below:

split columns in power bi using dax
power bi split a column into multiple columns

Split the Column by Non-Digit to Digit.

split column value in power bi
power bi split column into multiple columns

The Power BI report will look like below:

split column data in power bi
how to split columns in power bi

What we have done changes will not affect to PowerBI desktop page until you did not press the “Close and Apply” button.

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

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:

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.

>