In this blog post, we explore different techniques of splitting column values in Power Query. This common data transformation technique is applicable in Microsoft Power BI and Excel and should help you when dealing with datasets that contain combined or structured information.
For a full overview of Power Query, be sure to check out our Power Query 101: A Complete Overview video on YouTube.
Note: while screenshots presented here are from Power Query inside of Power BI, the steps are the same if you are accessing it through Excel.
Why Split Column Values in Power Query?
Business data captured in databases can sometimes contain combined or structured information, which may need to be segregated for better analysis in Power BI or Excel. This can include splitting columns with codes, parsing delimited values, or separating alphanumeric strings.
For example:
- A manufacturer analyzing its production may be faced with a [Product ID] column containing a code TREK-BRK-1750, which needs to be parsed to get the corresponding client (TREK), the part category (BRK), and the part number (1750).
- An address column combining street, city, and postal code might need splitting into separate columns for better categorization.
Splitting columns effectively allows you to refine the data for analysis and visualization. For instance, with the three additional columns in the first example, the manufacturer can understand production by client, part category, and specific parts manufactured.
With Power Query, in Power BI or Excel, the process of splitting column values is a simple task that requires no coding knowledge.
Steps to Splitting Column Values in Power Query
To split column values in Power Query, select the Transform tab and expand the Split Column button under the Text Column section. Doing this will display seven non-coding ways of splitting column values in Power Query. These include:
By Delimiter
Splits a column value into multiple columns by splitting on a delimiter. This can be one of the standard delimiters available from the Split Column by Delimiter dialog box or a custom delimiter provided by you. This would be the method used to split the example column above where information was combined and separated by a dash (-).
Use Case: Parsing structured values like product codes, addresses, or tags separated by commas or dashes.
By Number of Characters
Splits the column at fixed intervals, where each segment contains a specific number of characters. Ideal for data with uniform structure, like serial numbers or codes.
Example: A 12-character serial number like 123456789012 split into three parts: 1234, 5678, and 9012.
By Positions
Splits existing columns into multiple columns based on specific character positions within the data, allowing precise control over where the splits occur. Useful for structured data like account numbers or IDs.
Use Case: Structured data like account numbers or IDs where the position of each component is consistent.
By Lowercase to Uppercase
Splits whenever a lowercase letter is immediately followed by an uppercase letter, which can help separate camelCase or PascalCase text into distinct words.
Example: Splitting firstNameLastName into firstName and LastName.
By Uppercase to Lowercase
Splits wherever an uppercase letter is followed by a lowercase letter, which can be useful in specialized cases like certain formatting in titles or headers.
Example: Splitting NAMEtitle into NAME and title.
By Digit to Non-Digit
Splits whenever a numeric character transitions to a non-numeric character, ideal for separating data that mixes numbers and text, such as product codes.
Example: Splitting ABC123 into ABC and 123.
By Non-Digit to Digit
Splits at the transition from text to numeric characters, similarly useful for separating mixed data like alphanumeric identifiers.
Example: Splitting Product567 into Product and 567.
Common Challenges and Solutions
While splitting columns is straightforward, there are potential challenges to consider:
- Missing Delimiters: Ensure the delimiter exists in all rows. If some rows are missing the delimiter, they may not split correctly, resulting in null values.
- Empty or Null Values: Decide how to handle rows with missing data to avoid errors in further transformations.
- Inconsistent Splits: When splitting by positions or characters, verify that all rows conform to the expected format.
Tip: Use the “Replace Values” or “Conditional Column” feature in Power Query to handle exceptions before splitting. For a tutorial on replacing column values, check out our Replace Multiple Column Values in One Step in Power Query video on YouTube.
Wrapping It Up...
The Power Query user interface offers several options to split values from a column into two or more columns without any prior knowledge of the M programming language (the programming language behind Power Query). By using this feature, Power Query users in Power BI or Excel can quickly perform the crucial data transformation task of splitting structured data from their existing datasets.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
![Read more about the article [How To] Create a histogram and cumulative frequency distribution chart in Power BI](https://goanalyticsbi.com/wp-content/uploads/2022/12/Blog-Feature-Image-Template-2-300x157.png)