Split Columns in Power BI
Splitting columns refers to the process of dividing a single column into multiple columns based on specific criteria. This technique allows you to extract valuable information from a column containing multiple data points and split them into separate fields for analysis. Splitting columns in Power BI provides immense flexibility and opens up a world of possibilities for data manipulation and transformation.
Uses of Split Columns in Power BI?
Splitting columns in Power BI is a powerful tool that offers numerous benefits for data analysis and visualization. Here are a few reasons why you should leverage this feature:
Enhanced Data Organization: Splitting columns allows you to organize your data more effectively by separating different pieces of information into their own fields.
Improved Data Analysis: By splitting columns, you can access and analyse specific data points with ease, enabling deeper insights and more accurate reporting.
Streamlined Data Transformation: Splitting columns simplifies the process of transforming raw data into a more structured and meaningful format, saving you time and effort.
Now that we understand the importance of split columns, let’s explore some practical use cases and learn how to implement this feature effectively in Power BI.
To demonstrate this Power BI split columns options, we are going to use the DemoTable.
As you can see in the image, the DemoTable has only one column. Now, we want to split this column as per the values inside column.
To split column in an existing table, Click on “Transform Data” in “Home” tab. It will open Power Query Editor.
Before we start splitting the columns in power BI, Please make sure that you have created a duplicate column.
For creating duplicate column, right-click on the column name and select Duplicate column option.
When we clicked on Duplicate table a duplicate table has been created.
Now, to split the column, right click on the column that we want to split and click on “Split column”.
In the “Split Column” dialog box, you have several options to split the column:
Split by Delimiter
This option allows you to split the column based on a delimiter character. You can specify the delimiter character (e.g., comma, space) in the provided field. Power BI will split the column wherever it encounters the specified delimiter.
Split by Number of Characters
With this option, you can split the column based on a specific number of characters from either the left or right side. You need to choose whether you want to split from the left or right and enter the desired number of characters.
Split by Position
This option enables you to split the column based on specific character positions. You can enter the starting and ending positions for each split in the provided fields. Power BI will extract the characters within the specified positions into separate columns.
Split by Uppercase to Lowercase Transition
Selecting this option will split the column whenever there is a transition from an uppercase letter to a lowercase letter. It is helpful for splitting column values following camel case or Pascal case conventions.
Split by Lowercase to Uppercase Transition
This option splits the column when there is a transition from a lowercase letter to an uppercase letter. It can be used for splitting column values following snake case or kebab case conventions.
Split by digit to non-digit
Choosing this option will split the column wherever there is a transition from a digit (numeric character) to a non-digit character (e.g., letter, symbol, whitespace). Power BI will create separate columns, extracting the digits and non-digits into their respective columns. This can be useful when you have a column with alphanumeric values and want to separate the numeric part from the non-numeric part.
Example: Original column: “ABCD1234XYZ” Split column result:
Column 1: “ABCD”
Column 2: “1234XYZ”
Split by non-digit to digit
Selecting this option will split the column wherever there is a transition from a non-digit character to a digit character. Power BI will generate separate columns, extracting the non-digits and digits into their respective columns. This option is helpful when you want to separate letters, symbols, or whitespace from numeric values in a column.
Example: Original column: “ABCD1234XYZ” Split column result:
Column 1: “ABCD”
Column 2: “1234XYZ”
We will use all of these options one by one, for splitting column.
By Delimiter:
Click on “By Delimiter” option.
When we click on By Delimiter, the following window will open.
In this window you get the following options:
- Select or enter delimiter: This option allows you to specify the delimiter character or characters at which you want to split the column. If it is not there in the list, then select the Custom option and specify that custom character.
- Leftmost delimiter: This choice divides the string from the leftmost position up to the first delimiter.
- Rightmost delimiter: This option divides the string from the rightmost position starting from the last delimiter.
- Every occurrence of the delimiter: The text is split at every instance of the delimiter.
We are selecting “Comma” as a delimiter and “the Left-most delimiter”. And under the “Advanced options” you can specify the number of Columns or Rows.
And at the end just click on OK button.
Now you can see that employee Id’s have separated from the original column and it placed into a new column.
And now, we are selecting split at “Right-most delimiter”.
As you can see in the following image Phone No. have been separated from the original column and placed into the new column.
At the end, we are selecting split at “Each occurrence of the delimiter”.
In the “Advanced options” you can specify the number of Columns or Rows.
Now you can see, text at each occurrence of the comma is separated from the original column and placed in a new column.
This is how we can split the column By Delimiter.
Split columns By Number of Characters:
Now we will click on “By Number of Characters”.
As we click on “By Number of Characters”, it opens a new window.
In this window you get the following options:
- Number of Characters: Please indicate the quantity of characters utilized for column splitting.
- Once, as far left as possible: This choice divides the leftmost string before reaching the specified number of characters.
- Once, as far right as possible: This option divides the rightmost string after reaching the specified number of characters.
- Repeatedly: The text is split into segments of “Number of characters” each.
We set the “Number of Characters” quantity as 2 and select split “Once, as far left as possible”.
Let’s see the result:
Now we set the “Number of Characters” quantity as 14 and select split “Once, as far Right as possible”.
Let’s see the split column:
Now we set the “Number of Characters” quantity as 5 and select split “Repeatedly”.
Split columns:
Split column By Position:
Now we will click on “By Position”.
Now, a new window “Split Column by Position” has opened.
As you can see in “Split Column by Position” window, there is a position option, in this box we write two arguments i.e. starting position and Ending position.
Starting Position: This component allows you to specify the starting character position from which you want to split the column. Enter the desired starting position in the provided field. Power BI will begin the split from the specified character position.
Ending Position: This component allows you to specify the ending character position at which you want to stop the split. Enter the desired ending position in the provided field. Power BI will split the column up to the specified character position.
As you can see in the above image, I used 0 as Starting position and 1 as Ending position.
Now let’s see the result:
You can see, the character of the string which were starting from position 0 and ending at position 1, has been Split in a separate column.
Split column By Lower Case to Upper Case:
When we select the “By Lower Case to Upper Case” option, Power BI will examine the values in the column and identify points where there is a change from a lowercase letter to an uppercase letter. It will then split the column at those points, creating separate segments based on the transition.
In our table there is no changes from lowercase letter to an uppercase letter. So we will get only null values.
Split column By Upper Case to Lower Case:
When we choose the “By Upper Case to Lower Case” option, Power BI examines the values in the column and identifies points where there is a change from an uppercase letter to a lowercase letter. It then splits the column at those points, creating separate segments based on the transition.
For example, let’s consider the original column: “LearnSuperEasy”. Using the “By Upper Case to Lower Case” option, Power BI identifies the transition points between uppercase and lowercase letters (“Learn”, “Super”, “Easy”). The column is split accordingly into segments based on these transitions.
If we split, by choosing the “By Upper Case to Lower Case” in our Demo table, then Split columns would be:
Split column By Digit to Non-Digit:
When we select the “By Digit to Non-Digit” option, Power BI examines the values in the column and identifies points where there is a change from a digit to a non-digit character. It then splits the column at those points, creating separate segments based on the transition.
For example, let’s consider the original column: “ABC123XYZ456”. Using the “By Digit to Non-Digit” option, Power BI identifies the transition points between digits and non-digits (“ABC”, “123”, “XYZ”, “456”). The column is split accordingly into segments based on these transitions.
Let’s split the Demo table’s column using “By Digit to Non-Digit” option.
Split column By Non-Digit to Digit:
When we choose the “By Non-Digit to Digit” option, Power BI examines the values in the column and identifies points where there is a change from a non-digit character to a digit. It then splits the column at those points, creating separate segments based on the transition.
For example, let’s consider the original column: “XYZ456ABC123”. Using the “By Non-Digit to Digit” option, Power BI identifies the transition points between non-digits and digits (“XYZ”, “456”, “ABC”, “123”). The column is split accordingly into segments based on these transitions.
Let’s split the Demo table’s column using “By Non-Digit to Digit” option.
Conclusion:
In conclusion, splitting a column in Power BI is a powerful feature that allows you to divide a single column into multiple segments based on specific criteria. By splitting a column, you can extract valuable information, organize data, and enable further analysis and visualization. Power BI provides several options for splitting columns, including splitting by delimiter, number of characters, position, or specific character patterns. Each option offers different ways to divide the column and create new columns or replace the current column with the split results. Splitting a column is beneficial when you need to extract specific data, normalize values, perform calculations on segments, or improve data organization within Power BI. It is a versatile tool that enhances your data analysis capabilities and facilitates data-driven decision-making.