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.

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)

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

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.

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.

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

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

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.

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))

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.

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

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

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.

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.

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

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.

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.

• Now, you can see the column gets split into two columns i.e. First name and Last name columns.

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.

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.

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

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

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.

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.

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

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.

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.

Once you click on ok you can see the date-time column get splitter into two-column and you can rename the 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.

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.

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

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.

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.

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

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.

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.

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

• Then select Column 1 and click on Transpose from the ribbon, you can see the column get splits into multiple rows.

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.

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.

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

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

You may like the following Power BI tutorials:

• Power BI Visualizations
• Built-in Date Hierarchy in Power BI
• PowerBI Report Filter using Slicer Visualization
• Power BI Query Group
• Power BI Change Data Type of a Column
• Power BI Date Slicer

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