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.

Split Column in Power BI

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.

Power BI split column in dax

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.

how to split column in power bi using dax

2. Under the Modeling tab, click New column.

Power BI split in dax

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.
dax split in Power BI

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

Power BI split column dax

Now, we create another column called Product Type.

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

power bi dax split

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.
dax split column by delimiter

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

power bi split column by delimiter dax

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

See also  How to add an empty column in Power BI

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:

Power BI Split Column but Keep Original

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.

Split column but keep original in Power BI

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

split column but keep original power bi

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

Microsoft power bi split column but keep original

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

power bi Power Query split column but keep original

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.

split column but keep original using Power Query

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

power bi split column but keep original using Power Query

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.

Power BI split column in dax

Now follow the below steps:

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

power bi split string

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

split column in power bi

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

how to split text in power bi

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.

how to split column in power bi

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

split in power bi

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.

splitting columns in power bi

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

how to split columns in power bi

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.

split column by delimiter power bi

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

split column power bi

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

See also  What if parameter Power BI Date

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:

split text in power bi

Now follow the below steps:

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

power bi split text

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

split string power bi

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.

power bi string split

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

how to split a column in power bi

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

powerbi split

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

powerbi split string

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.

Power BI split column in dax

Now follow the below steps:

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

how to split date and time in power bi

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

power query split date and time

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.

power bi split date column into month and year

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

power bi split date and time

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

power bi separate date and time

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:

ColumnsData Types
Customer IDSingle line of text
Customer NameSingle line of text
Full AddressSingle line of text
power bi split column into rows

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

power bi split column by delimiter into rows

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

power bi dax split text by delimiter

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.

power bi split column by value

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.

power query split column by delimiter

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

power bi split cell into multiple rows

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

power bi split column

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

how to split data in power bi

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.

split columns in power bi

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

power query split row into multiple rows

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

See also  How to Append Columns in Power Query

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:

Power BI split column by multiple delimiters

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

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

Microsoft Power BI split column by multiple delimiters

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

Power BI split column by multiple delimiters using Power Query

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

Power BI split columns by multiple delimiters

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

Power BI split columns by multiple delimiters using Query editor

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:

power bi split column into multiple rows

Now follow the below steps:

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

Split column into multiple rows in power bi

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

power bi split column into rows

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.

power bi split column by delimiter into rows

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

power BI dax split string into rows

Also, you may like:

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
>