Power BI split column [With 13 real examples]

Do you want to know how to split a column in power bi? Here is a complete tutorial on Power BI split column with a few examples. After completion of this tutorial, you will get to know how to split columns in power bi? Here we have covered:

  • Power BI split column by delimiter Dax
  • Power BI split column by position
  • power bi split column by delimiter
  • Power BI split column by multiple delimiter
  • Power BI split column into multiple columns
  • Power BI split column by text contains
  • Power BI split column but keep original
  • Power BI split column by comma
  • Power BI split date time column
  • Power BI split column by number of characters
  • Power BI split column by new line
  • Power BI split column into multiple rows
  • power query split column into rows

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 column in Power BI

There are different ways, we can split columns in Power BI like we can use DAX and Power Query also.

Here we will take a few examples of power bi split column and we will see how it works.

Power BI split column by delimiter Dax

Here we will see how to split column by delimiter using DAX in Power BI.

For example, we will use the below table and we will split the Name column to get the First Name.

power bi split column by delimiter dax
power bi split column by delimiter Dax

To get the First name column by splitting the Name column, follow the below steps:

  • In Power BI Desktop, create the Custom column by clicking on the New column from the ribbon.
  • In the Formula bar, write the below formula to split the column by delimiter:
First name = LEFT('Table'[Name],FIND(" ",'Table'[Name])-1)
power bi split column by delimiter using dax
power bi split column by delimiter using Dax

Now you can see a column get added to the table having the First name value.

power bi split column
power bi split column

This is an example of Power BI split column by delimiter dax.

Read Power Query Date

Power BI split column by position

Here we will see how to split column by position using Power Query in Power BI.

For example, we will use the below table and we will split the product id column by position.

power bi split column by position
power bi split column by position

To split the column by position into columns using Power Query in Power BI, follow the below steps:

  • In the Power Query editor, select the column and click on the Split icon from the ribbon -> select By position from the list.
power bi split column
power bi split column
  • Now split column by position window will open. Under position provide 0,2, whereas positions are zero-based and comma-separated.
  • Then expand the advanced option, and select the Column option under Split into.
split column power bi
split column power bi

Once you click on OK, you can see the column is split into two-column based on the position provided.

power bi split by delimiter
Microsoft power bi split column by position

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

Read Power Query Examples

Power BI split column by multiple delimiters

Here we will see how to split columns by multiple delimiters using Power Query in Power BI.

For example, we will use the below table and we will split the model column and capacity column with delimiter ‘-‘, ‘&’ using Power Query.

Power BI split column by multiple delimiters
Power BI split column by multiple delimiters

To split the column by multiple delimiters into rows using Power Query, follow the below steps

  • In Power Query, go to Add column tab -> select a custom column from the ribbon.
  • The custom column window will open, provide the column name as Table, and add the below formula to the formula box. This formula returns the table with a split column value.
Table.FromColumns({
     Text.Split([Installed model],"-"),
     Text.Split([Capacity],"&"),
     Text.Split([Capacity],"&")},

Table.ColumnNames(Source))
power bi split column by delimiter
power bi split column by delimiter

Once you click on Ok, a column with a table is added to the existing table, so we will remove all other columns except that column. To remove, select the columns by pressing Ctrl + click columns. Then click on Remove columns from the ribbon.

Power BI split column by multiple delimiters using Power Query
Power BI split column into new table

Now the column gets removed, click on the double arrow icon to expand the table. Select the Expand option and click on Ok.

power bi split column by multiple delimiter
Power BI split columns by multiple delimiters using Power Query

Now you can see the columns get split into multiple delimiters in the Power Query editor.

Power BI split columns by multiple delimiters using Query editor
power bi split column by multiple delimiter

This is an example of Power BI split column by multiple delimiter.

Read Create table using Power Query in Power BI

Power BI split the column into multiple columns

Here we will see how to split the column into multiple columns using Power Query in Power BI.

For example, we will use the below table, and we will split the date column into multiple columns( day, month, and year columns) using Power Query.

Power BI split the column into multiple columns
Power BI split the column into multiple columns

To split the date column into multiple columns, follow the below steps

  • In Power Query Editor, select the date column, then click on the Split Column option from the ribbon -> select Split column by delimiter.
  • Now split column window will open, Under select or enter delimiter -> select custom -> provide the delimiter as ‘-‘.
  • Under Split at, select 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. In this case, it is 3. Click on OK.
Microsoft Power BI split the column into multiple columns
Microsoft Power BI split the column into multiple columns
  • Once you click on Ok, you can see the date column split into 3 columns, then you can rename the column into Month, day, and Year.
power bi split column into multiple columns
power bi split column into multiple columns

This is an example of Power BI split column into multiple columns.

Read Power BI if date + 27 Examples

Power BI split column by text contains

Here we will see how to split column by text contains using Power Query in Power BI.

For example, we will use the below table, to split the name column by Lowercase to the uppercase split option.

power bi split column by text contains
power bi split column by text contains

To split the Name column having text contains using ‘By Lowercase to uppercase’ using Power Query, follow the below steps:

  • In Power Query Editor, select the column -> then select the Split column from the ribbon -> By Lowercase to Uppercase option.
Microsoft power bi split column by text contains
Microsoft power bi split column by text contains
  • Now, you can see the column gets split into two columns i.e. First name and Last name columns.
Microsoft power bi split column by text contains using Power Query
Microsoft power bi split column by text contains using Power Query

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

Read Power BI IF + 41 Examples

Power BI split column but keep original

Here we will see how to split the column while keeping the original column as it is using Power Query in Power BI.

For example, we will use the below table and we will split the Name column by keeping the column as it is, for this we need to duplicate the column and split the column.

power bi split column but keep original
power bi split column but keep original

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

  • In Power Query, select the column -> go to Add column tab -> Duplicate column from the ribbon.
Microsoft power bi split column but keep original
Microsoft power bi split column but keep original
  • Next, we will split the column with a delimiter, so select the duplicate column and click on the Split column from the ribbon -> By split as a delimiter.
  • Then Split column by delimiter is open, select the delimiter as Space, and under Split at select Each occurrence of delimiter as an option.
Microsoft power bi split column but keep original using Power Query
Microsoft power bi split column but keep original using Power Query
  • 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
power bi split column but keep original using Power Query

This is an example of Power BI split column but keep original.

Read Power BI Pie Chart

Power BI split column by comma

Here we will see how to split column by comma using the Power Query in Power BI.

We will use the below table to split the Location column based on the comma using Power BI.

power bi split column by comma
power bi split column by comma

To split the Location column based on the comma using Power Query, follow the below steps:

  • In Power Query Editor, select the Location column then click on the Split column icon -> select Split by Delimiter from the option.
  • Split Column By Delimiter window will open, then provide, Select Delimiter -> Comma, Split at -> Each occurrence of the delimiter. Click on OK.
Microsoft power bi split column by comma
Microsoft power bi split column by comma

Once you click on ok, the Location column is split into two parts, and then rename the column into City and Country.

Microsoft power bi split column by comma using Power Query
Microsoft power bi split column by comma using Power Query

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

Power BI split date time column

Here we will see how to split the date-time column using Power Query in Power BI.

For example, we will use the below table and split the date column using Power Query.

power bi split date time column
power bi split date time column

To split the date and time column using Power query follow the below steps:

  • In Power Query Editor, select the date and time column -> to click on the Split column -> select Split by delimiter.
  • Then Split column by delimiter window will open, then select the delimiter as Space, and select the Left-most delimiter under the Split at section. Click on Ok.
Microsoft power bi split date time column
Microsoft power bi split date time column

Once you click on ok you can see the date-time column get splitter into two-column and you can rename the column.

power bi split date time column
power bi split date time column

This is an example of Power BI split date time column.

Read Power BI Switch

Power BI split column by number of characters

Here we will see how to split a column by number of characters using Power Query in Power BI.

For example, we will use the below table to split the location column. We will split the last three characters i.e. USA.

power bi split column by number of characters
power bi split column by number of characters

To split the location column by the number of characters using Power Query, follow the below steps:

  • In Power Query Editor, select the Location column -> click on Split Column icon -> select By number of characters from the option.
  • Then Split the column by numbers of characters window will open, then provide the number of characters i.e. 3 and select ‘Once, as far right as possible,’ click on OK.
Microsoft power bi split column by number of characters
Microsoft power bi split column by number of characters

Once you click on ok, you can see the three characters from the right side get splits.

Microsoft power bi split column by number of characters using Power Query
Microsoft power bi split column by number of characters using Power Query

This is an example of Power BI split column by number of characters.

Read Power BI Measure Sum and Subtract Example

Power BI split column by new line

Here we will see how to split column by new line or line break using Power Query in Power BI.

For example, we will use the below table and split the Employee name column using a split column by a new line in Power query Editor.

power bi split column by new line
power bi split column by new line

To split the column by a new line in Power Query Editor, follow the below steps

  • In Power Query Editor, select the Employee name column, and then click on the Split column option -> select the By Delimiter option.
  • Then, Split Column By Delimiter window will open, select the Custom option for the delimiter, select Each occurrence of delimiter under Split at, and then tick the Split using Special character option, then insert special character as Line Feed. Click on Ok.
Microsoft power bi split column by new line
Microsoft power bi split column by new line

Once you click on ok, the column gets splits by a new line, then rename the column.

Microsoft power bi split column by new line using Power Query
Microsoft power bi split column by new line using Power Query

This is an example of Power BI split column by new line.

Read Remove blank from Power bi slicer

Power BI split column into multiple rows

Here we will see how to split a column into multiple rows using Power Query in Power BI.

We will use the below table and we will split the columns into multiple rows.

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

To split the column into multiple rows using Power Query, follow the below steps:

  • In Power Query Editor, select column 1 -> go to Transform tab -> select Transpose option and you can see the table get transposed.
Using power bi split column into multiple rows
Using power bi split column into multiple rows
  • Now select Column 2 and go to the Home tab -> click on Split Column -> select By Delimiter.
  • Split column by delimiter window will open -> select the delimiter as Comma, -> select Each occurrence of delimiter. Click on Ok.
Microsoft power bi split column into multiple rows
Microsoft power bi split column into multiple rows
  • Then select Column 1 and click on Transpose from the ribbon, you can see the column get splits into multiple rows.
Microsoft power bi split column into multiple rows using Power Query
Microsoft power bi split column into multiple rows using Power Query

This is an example of Power BI split column into multiple rows.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power query split column into rows

Here we will see how to split columns into rows using Power Query in Power BI.

For example, we will use the below table and split the Name column into multiple rows using Power Query.

power query split column into rows
power query split column into rows

To split the column into rows using Power Query, follow the below steps:

  • In Power Query Editor, select the Name column -> click on Split Column from the ribbon -> select By delimiter option.
  • Now Split column by delimiter window will open, so select the delimiter as Space, Under Split at select Left most delimiter, and expand the Advanced option -> select Rows. Click on Ok.
Using power query how to split column into rows
Using power query how to split column into rows

Now you can see the name column get split into multiple rows using Power Query.

 how to split column into rows Using power query in Power Bi
how to split column into rows Using power query in Power Bi

This is an example of Power BI split column into multiple rows

You may like the following Power BI tutorials:

Conclusion

In this Power BI Tutorial, we learned all about split columns in Power BI using DAX and also using Power Query Editor with different examples, which are listed below.

  • Power BI split column by delimiter Dax
  • Power BI split column by position
  • power bi split column by delimiter
  • Power BI split column by multiple delimiter
  • Power BI split column into multiple columns
  • Power BI split column by text contains
  • Power BI split column but keep original
  • Power BI split column by comma
  • Power BI split date time column
  • Power BI split column by number of characters
  • Power BI split column by new line
  • Power BI split column into multiple rows
  • Power query split column into rows
>