Power BI Split Column by Text Contains | Power BI Split String

When working in Power BI, you might encounter a situation where your data has merged columns, such as having too much information in one column, like “PowerBIDesktop.“

In that case, you can use Power BI’s Split Column option to divide that column into multiple columns, like “Power BI Desktop.“

In this tutorial, we will learn about Power BI Split Column by Text Contains, Power BI split string, and Power BI Split Column but Keep Original. Also, we will cover the topics below:

• What is Split Column in Power BI
• Power BI split column by delimiter DAX
• Power BI split column by position
• Working with Power query split column by delimiter
• Power BI split date column into month and year
• Power BI split columns by the number of characters
• How to Power BI split column into multiple rows
• Power BI split column by multiple delimiter

Split Column in Power BI

Power BI Split Column means dividing one column into multiple columns based on the presence of specific words or phrases within that column.

Example:

If you have a column containing full names like “John Doe” and “Jane Smith,” and you want to split it into two columns for first names and last names, you can do so by identifying the space between the first and last names as the delimiter. Refer to the image below.

Power BI Split Column by Delimiter DAX

Imagine having a Power BI dataset that includes information about your products, such as their Names, Dates, and prices.

Using Power BI’s “Split Column by Delimiter DAX” feature, you can easily separate the product names into different columns, such as Product Type and Product Color.

To do this, follow the below steps:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Under the Modeling tab, click New column.

3. In the formula bar, put the below expression. Then click the commit button.

Product Color = LEFT('ProductSales'[Product Name],FIND(" ",'ProductSales'[Product Name]))

Where:

• Product Color = This is the name we’re giving to the new column that we are creating.
• LEFT() = This DAX function extracts a specified number of characters from a text string’s beginning (left side).
• ProductSales = This is the name of the table where our data is presented.
• [Product Name] = This is the name of the column where we want to split.
• FIND(” “, ‘ProductSales'[Product Name]) = This part finds the position of the first space character within the “Product Name” column.

4. When you go to the Table view, you can see a Product Color column is created.

Now, we create another column called Product Type.

5. Under the Table tools, click the New column.

6. In the formula bar, put the below expression. Then click the commit button.

Product Type = 
    RIGHT('ProductSales'[Product Name], LEN('ProductSales'[Product Name]) - FIND(" ", 'ProductSales'[Product Name]))

Where:

• Product Type = This is the name we’re giving to the new column that we are creating.
• RIGHT(‘ProductSales'[Product Name], …) = This part of the expression selects characters from the right side of the text in the “Product Name” column of the “ProductSales” table.
• LEN(‘ProductSales'[Product Name]) = This calculates the total length (number of characters) of the text in the “Product Name” column.
• FIND(” “, ‘ProductSales'[Product Name]) = This finds the position of the first space character (” “) within the text of the “Product Name” column.
• LEN(‘ProductSales'[Product Name]) – FIND(” “, ‘ProductSales'[Product Name]) = This calculates the number of characters from the first space to the end of the text in the “Product Name” column.

7. Then, you can see a Product Type column is created.

This way, you can split the column by delimiter in Power BI DAX.

Power BI Split Column but Keep Original

Here, we see how to split a column while keeping the original column unchanged using Power Query in Power BI.

For this example, I use below table:

To split the column but keep the original column, follow the below steps:

1. First, open the Power Query editor and then load the table mentioned above.

2. Select the column -> go to Add Column tab -> click Duplicate Column.

3. Now you can see the Name column duplicate with Name-Copy column.

4. Select the column, then expand the Split column menu under the Home tab and click on “By Delimiter.”

5. Then the “Split Column by Delimiter” window opens. Select the delimiter as Space, and under “Split at,” choose “Each occurrence of delimiter” as an option. Then click OK.

6. Now you can see the column gets split by keeping the original column as it is.

This way you can split a column while keeping the original column unchanged using Power Query in Power BI.

Power BI Split Column by Position

Let’s see how to split columns by position using Power Query in Power BI.

In this example, we will use the same table as mentioned earlier.

Now follow the below steps:

1. Open Power BI Desktop, then under the Home tab, click Transform data.

2. In the Power Query Editor, I hope you uploaded the data.

3. Select the column, then expand the Split column menu under the Home tab and click on “By Positions.”

4. Now, the “Split Column by Position” window will open. Under “Position,” provide 0,2, remembering that positions are zero-based and comma-separated. Then, expand the advanced options and select the “Column” option under “Split into.” Then click OK.

5. You’ll see that the column is split into two columns based on the positions provided.

This is an example of Power BI split column by position.

Power Query Split Column by Delimiter

In this example, we see how to split columns by delimiter in the Power Query editor.

1. Open Power Query Editor; I hope you uploaded the data.

2. Select the column, then expand the Split column menu under the Home tab and click on “By Delimiter.”

3. Then the “Split Column by Delimiter” window opens. Select the delimiter as Space, and under “Split at,” choose “Each occurrence of delimiter” as an option. Then click OK.

4. Now, you can see the column gets split.

This way, you can split columns by delimiter in the Power Query editor.

Power BI Split Column by Text Contains

Let’s see how to split columns by text using Power Query in Power BI.

In this example, we will use below table:

Now follow the below steps:

1. Open Power Query Editor. Under the Home tab, expand New Source and click Excel Workbook.

2. This will ask you to browse an Excel file. Here, select the Excel file and click on Open.

3. After that, it will display all the tables in the Excel file you created. Select the table you want to use for creating the report. Click on OK.

4. Then, you can see our load in the power query editor.

5. Select the column, then expand the Split column menu under the Home tab and click on “By Lowercase to Uppercase.”

6. Now, you can see the column gets split into two columns.

This is an example of Power BI split column by text contains.

Power BI Split Date Column into Month and Year

Let’s see how to split the column into multiple columns using Power Query in Power BI.

In this example, we use the table below.

Now follow the below steps:

1. Open Power Query Editor; I hope you uploaded the data.

2. Select the Date column, then expand the Split column menu under the Home tab and click on “By Delimiter.”

3. Now, the “Split Column by Delimiter” window will open. Under “Select or enter delimiter,” select “Custom” and provide the delimiter as ‘–.’ Under “Split at,” select the “Each occurrence of the delimiter” option. Expand the Advanced options under “Number of columns to split into,” provide the number of columns you want to split, which in this case is 3. Then click on OK.

4. You will notice that the date column is split into 3 columns.

5. Then you can rename the columns to “Month,” “Day,” and “Year.”

This is an example of splitting a column into multiple columns using Power BI.

Power BI Split Columns by Number of Characters

In this example, we see how to split a column by the number of characters.

Scenario:

Your company collects customer addresses in a single column, but you need to analyze the data based on specific parts of the address, such as the city, state, and zip code.

According to this scenario, we have a SharePoint list named Customer Addresses that contains the following columns with various data types:

Columns	Data Types
Customer ID	Single line of text
Customer Name	Single line of text
Full Address	Single line of text

1. Open Power Query Editor. Then, under the Home tab, expand New Source, click More…

2. First, the “Get Data” window will Open. Next, choose “Online Services,” then “SharePoint Online List,” and finally click on “Connect.”

3. This SharePoint Online Lists will open a dialog box where you can enter the SharePoint site URL. Click ‘OK’ to proceed. If you create a report for the first time, it will prompt you to enter your username and password to access the SharePoint online list or site.

4. After that, it will display all the lists and libraries on the SharePoint site. Select the list you want to use in this Power BI report, and then click OK.

5. Now, you see all columns loaded in the Power query editor.

6. Then, under the Home tab, click the Choose Columns option. The Choose Columns dialog box opens. By default, Power BI will load all the list columns. You can choose the columns you need to create a report. Then click ‘OK’.

7. Select the column, then expand the Split column menu under the Home tab and click on “By Number of Characters.”

8. Then, a window will open. Split Column by Number of Characters. ‘ Provide the Number of characters, e.g., 5, and select ‘Once, as far right as possible. ‘ Click on OK.

9. Then, you can see the zip code split in another column.

This way, you can split the column by the number of characters.

Power BI Split Column by Multiple Delimiter

Let’s see how to split columns by multiple delimiters using Power Query in Power BI.

For this example, I am using below table:

To split the column into rows using Power Query with multiple delimiters, do this:

1. I hope you load data in Power Query Editor.

2. Choose the column you want to split. Then, go to the “Home” tab and click on “Split Column.” After that, select “By Delimiter.”

3. Once you’ve opened “Split Column by Delimiter,” select or type in the delimiter, such as a Comma. Make sure to choose “Split at -> Each occurrence of the delimiter. Then, “OK“.

4. Then you can see in the Power Query editor three columns.

Power BI Split Column into Multiple Rows

Let’s see how to split a column into multiple rows using Power Query in Power BI.

For this example, I am using below table:

Now follow the below steps:

1. Open the Power Query editor and put the above table.

2. Choose the column you want to split. Then, go to the “Home” tab and click on “Split Column.” After that, select “By Delimiter.”

3. Once you’ve opened “Split Column by Delimiter,” select or type in the delimiter, such as a Comma. Make sure to choose “Split at -> Each occurrence of the delimiter.” Then, expand the “Advanced options” and select “Rows” under “Split into.” Finally, click “OK” to complete the process.

4. Then, you can see a column split multiple rows using Power Query in Power BI.

Also, you may like:

• Power BI Date Slicer
• Power Query Date Functions
• Power Query Create Table in Power BI
• Power BI If Date is Greater than Specific Date
• Switch in DAX
• If Contains Power BI

In this tutorial, we’ve looked at how to use Power BI Split Column feature in different ways:

• Power BI split column by delimiter DAX
• Power BI split column by position
• Power query split column by delimiter
• Power BI splits the date column into months and year
• Power BI split columns by the number of characters
• Power BI split column by multiple delimiter
• Power BI split column into multiple rows