Introduction
Splitting column values into multiple columns in Power BI involves using the Power Query Editor to separate data based on specified delimiters. This process is useful for organizing and analyzing data that is aggregated into a single column.
Here’s an overview of how you can split column values into multiple columns in Power Query.
Ways of splitting column values in Power Query.
Splitting column values by delimiter.
Splitting column values by number of characters.
Splitting column values by positions.
1. Splitting column values by delimiter
This method is useful when you have a specific character (delimiter) that separates the values in your column - it could a space, comma, semicolon, tab, equal sign etc.
To split your data.
Open Power Query Editor by clicking on Transform Data from the Home tab.
Choose the column you wish to split (In this case, I am splitting the column Customer Name).
Select Split Column and choose By Delimiter.
In the dialogue box, enter your delimiter (In this case, I am going to set my delimiter as space – since the values I need to split are separated by a space). Next, I will select split at each occurrence of the delimiter. Note, under advanced section you can specify how many columns to split.
Executing this, will split the Customer Name at every occurrence of the delimiter leading to three columns as shown below.
2. Splitting columns values by number of characters
This method is useful when your data has a fixed-width, you can split it based on character count. For example, the column Country in this dataset, has a fixed width. Meaning, I can split it into two columns by specifying the number of characters as seven – which results to two columns one containing the phrase “United” and the other “States”.
To do so, repeat the steps above – but in this case select split by number of characters.
Choose the number of characters as seven.
Executing this leads to.
3. Splitting column values by positions
This method is useful when you want to split values at specific positions. For example, in this data set, I can extract the year values from the column Order ID using the split by position option. To do so, I will need to specify the positions as (3,7 – which splits the values at position 3, and 7 creating two columns one containing the year values).
To do so, repeat again the steps above – but in this case select split by positions.
Set the positions as 3 and 7 separated by a comma as shown below.
Executing this, separates the values into two columns, one containing the year values.
Conclusion
Splitting column values into multiple columns in Power BI is a straightforward process that enhances data clarity and usability. By utilizing the various options in Power Query, users can effectively manage and analyze their datasets according to their needs.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!