Data preparation is a crucial step in any Power BI project, and one of the most common challenges is dealing with columns that contain multiple values separated by delimiters. Whether you’re working with comma-separated names, pipe-separated categories, or any other delimiter-based data, knowing how to split columns using DAX can save you significant time and effort.
Understanding Column Splitting in Power BI
When working with data in Power BI, you’ll often encounter columns that contain multiple pieces of information separated by specific characters (delimiters). Common examples include:
- Full names separated by spaces: “John Smith”
- Email addresses with domains: “[email protected]”
- Product categories separated by commas: “Electronics,Laptops,Gaming”
- Geographic data with pipes: “USA|California|Los Angeles”
While Power Query provides excellent tools for splitting columns during data transformation, there are scenarios where you need to split columns dynamically using DAX calculations.
When to Use DAX vs Power Query for Column Splitting
Use Power Query When:
- You need to split columns permanently during data load
- The split logic is straightforward and won’t change
- You want to minimize model size and improve performance
Use DAX When:
- You need dynamic splitting based on user selections
- The splitting logic needs to be conditional
- You want to maintain the original column while creating split versions
- You’re working with calculated columns or measures
Basic DAX Functions for Column Splitting
Key DAX Functions
Before diving into examples, let’s understand the essential DAX functions for string manipulation:
- LEFT(): Extracts characters from the left side of a string
- RIGHT(): Extracts characters from the right side of a string
- MID(): Extracts characters from the middle of a string
- FIND(): Locates the position of a substring within a string
- SUBSTITUTE(): Replaces specific text within a string
- LEN(): Returns the length of a string
Method 1: Splitting Two-Part Strings
Let’s start with the most common scenario – splitting a full name into first and last names.
Example: Splitting Full Names
// Extract First Name
First Name =
LEFT(
Table[Full Name],
FIND(" ", Table[Full Name]) - 1
)
// Extract Last Name
Last Name =
RIGHT(
Table[Full Name],
LEN(Table[Full Name]) - FIND(" ", Table[Full Name])
)
Handling Cases with No Delimiter
To make your DAX more robust, handle cases where the delimiter might not exist:
// Safe First Name extraction
First Name =
IF(
ISERROR(FIND(" ", Table[Full Name])),
Table[Full Name],
LEFT(Table[Full Name], FIND(" ", Table[Full Name]) - 1)
)
// Safe Last Name extraction
Last Name =
IF(
ISERROR(FIND(" ", Table[Full Name])),
"",
RIGHT(Table[Full Name], LEN(Table[Full Name]) - FIND(" ", Table[Full Name]))
)
Method 2: Splitting Multi-Part Strings
For columns with multiple delimiters, you’ll need more sophisticated DAX formulas.
Example: Extracting Email Components
// Extract Username from Email
Username =
LEFT(
Table[Email],
FIND("@", Table[Email]) - 1
)
// Extract Domain from Email
Domain =
RIGHT(
Table[Email],
LEN(Table[Email]) - FIND("@", Table[Email])
)
// Extract Domain Name (without extension)
Domain Name =
VAR DomainPart = RIGHT(Table[Email], LEN(Table[Email]) - FIND("@", Table[Email]))
VAR DotPosition = FIND(".", DomainPart)
RETURN
IF(
ISERROR(DotPosition),
DomainPart,
LEFT(DomainPart, DotPosition - 1)
)
Method 3: Advanced Splitting with Multiple Delimiters
For complex scenarios with multiple different delimiters, you can use SUBSTITUTE to standardize delimiters first:
// Standardize delimiters and extract first part
First Part =
VAR StandardizedString = SUBSTITUTE(SUBSTITUTE(Table[Mixed Delimiters], "|", ","), ";", ",")
VAR FirstCommaPosition = FIND(",", StandardizedString)
RETURN
IF(
ISERROR(FirstCommaPosition),
StandardizedString,
LEFT(StandardizedString, FirstCommaPosition - 1)
)
Method 4: Creating a Generic Split Function
For repeated use, create a more generic approach:
// Generic function to get the Nth element from a delimited string
Nth Element =
VAR DelimitedString = Table[Categories]
VAR Delimiter = ","
VAR Position = 2 // Get the 2nd element
VAR AddDelimiter = Delimiter & DelimitedString & Delimiter
VAR StringLength = LEN(AddDelimiter)
VAR FirstOccurrence = FIND(Delimiter, AddDelimiter)
VAR SecondOccurrence = FIND(Delimiter, AddDelimiter, FirstOccurrence + 1)
VAR Result =
IF(
Position = 1,
MID(AddDelimiter, FirstOccurrence + 1, SecondOccurrence - FirstOccurrence - 1),
// Add logic for other positions as needed
""
)
RETURN Result
Method 5: Dynamic Column Splitting with Measures
Sometimes you need to split columns dynamically based on user selections:
Dynamic Split Result =
VAR SelectedSplitType = SELECTEDVALUE(SplitOptions[Split Type])
VAR SourceColumn = SELECTEDVALUE(Table[Source Column])
RETURN
SWITCH(
SelectedSplitType,
"First Name", LEFT(SourceColumn, FIND(" ", SourceColumn) - 1),
"Last Name", RIGHT(SourceColumn, LEN(SourceColumn) - FIND(" ", SourceColumn)),
"Domain", RIGHT(SourceColumn, LEN(SourceColumn) - FIND("@", SourceColumn)),
"No Selection"
)
Best Practices for Column Splitting with DAX
1. Error Handling
Always include error handling to prevent your reports from breaking:
Safe Split =
VAR SplitResult =
IF(
ISERROR(FIND(",", Table[Data])),
Table[Data],
LEFT(Table[Data], FIND(",", Table[Data]) - 1)
)
RETURN
IF(ISBLANK(SplitResult), "", SplitResult)
2. Performance Considerations
- Use calculated columns for static splits that won’t change
- Consider using measures for dynamic splits that depend on user interaction
- Pre-process complex splits in Power Query when possible
3. Data Quality Checks
Implement validation to ensure your splits are working correctly:
Split Validation =
VAR OriginalLength = LEN(Table[Original Column])
VAR SplitLength = LEN(Table[Part 1]) + LEN(Table[Part 2]) + 1 // +1 for delimiter
RETURN
IF(OriginalLength = SplitLength, "Valid", "Check Required")
Common Use Cases and Solutions
1. Geographic Data Splitting
Country = LEFT(Table1[Location], FIND("|", Table1[Location]) - 1)
State = MID(Table1[Location], FIND("|", Table1[Location]) + 1, FIND("|", Table1[Location], FIND("|", Table1[Location]) + 1) - FIND("|", Table1[Location]) - 1)
2. Product Code Parsing
Product Category = LEFT(Table1[Product Code], 3)
Product ID = RIGHT(Table1[Product Code], LEN(Table1[Product Code]) - 4)
3. Date and Time Separation
Date Part = LEFT(Table1[DateTime], FIND(" ", Table1[DateTime]) - 1)
Time Part = RIGHT(Table1[DateTime], LEN(Table1[DateTime]) - FIND(" ", Table1[DateTime]))
Troubleshooting Common Issues
Issue 1: “A function ‘FIND’ has been used in a True/False expression”
Solution: Wrap FIND functions in ISERROR() when using in IF statements.
Issue 2: Blank Results
Solution: Check for empty strings and handle them explicitly:
Result =
IF(
OR(ISBLANK(Table1[SourceCol]), Table1[SourceCol] = ""),
"",
// Your split logic here
)
Issue 3: Performance Problems
Solution: Consider moving complex splitting logic to Power Query or use variables to avoid repeated calculations.
Conclusion
You’ve now learned how to split columns by delimiters in Power BI using DAX! This a powerful technique that provides flexibility for dynamic data manipulation. While Power Query remains the preferred method for static transformations, DAX splitting becomes invaluable when you need conditional logic, user-driven splits, or when working with calculated columns and measures.
Remember to always include proper error handling, test your formulas with various data scenarios, and consider performance implications when choosing between DAX and Power Query approaches. With these techniques and best practices, you’ll be able to handle even the most complex column splitting requirements in your Power BI reports.
Start with simple two-part splits and gradually work your way up to more complex scenarios. The key is understanding how the basic string functions work together and building robust formulas that can handle edge cases in your data.
Keywords: How To Split Columns By Delimiters In Power BI Using DAX
