How to split by delimiter in Direct Query Power BI Reports?
This article explains you about how to achieve the split function in Direct Query Power BI reports. As you know, we can achieve the split function either in Power Query layer or in DAX but we don’t have a direction function called split in DAX but still we can some other functions which will help us to achieve the result.
Let’s take the below example.
EmpDetail column has Name, Email, Phone number & Department values but are delimited by “|”. We need to split them all and create separate columns.
Split in Power Query
Use “Each occurrence of the delimiter when you have multiple delimiter in your column. It will create multiple columns based on the number of delimiters.
Split function in DAX
We don’t have direct split function in DAX but we have different approaches to achieve the result but we need to create multiple columns as each DAX function is applies to one column.
We can use below functions to achieve the split.
- LEN
- LEFT
- RIGHT
- MID
- SUBSTITUTE
- TRIM
We also can use PATH functions to get the result. Refer below.
Name = PATHITEM(EmpDetails[EmpDetails],1)
Email = PATHITEM(EmpDetails[EmpDetails],2)
Phone = PATHITEM(EmpDetails[EmpDetails],3)
Department = PATHITEM(EmpDetails[EmpDetails],4)
But all the above will work without an issue when we have Import mode connectivity.
What will happen if we use Direct Query?
Let us first try with Power Query first,
If I use Split by column then we will end up with below message.
“This step results in a query that is not supported in DirectQuery mode.”
It means, as we are connecting datasources which are support direct query doesn’t support this Split bu delimiter function so we can’t use this method.
Let us try in DAX layer now. Again, we get same kind of message “Function PATHITEM is not allowed as part of calculated column DAX expressions on DirectQuery models.”
Do we have solution for this type scenario?
Yes. We still can achieve the expected result with the help of other DAX functions which I mentioned above.
Name =
VAR String_Length = LEN(EmpDetails[EmpDetails])
RETURN
TRIM (
LEFT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length
)
)
The above DAX script will give the name value from the EmpDetail column. We can do the same to split the other values.
Let me explain the above script to understand better.
I have created a variable to store the Length of the whole value. You still can create a new column for length and can be used in this script.
SUBSTITUTE section
SUBSTITUTE(EmpDetails[EmpDetails], “|”, REPT(” “,String_Length))
We are changing the “|” value into spaces. REPT is used to repeat the spaces with length size. The result will be like below.
There are total length spaces between each word.
LEFT Section
LEFT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length
)
Lets assume the length of the whole value is 35, Left function will show the 35 letters from the left position so we will get as like below.
It has extra spaces at the end of each value.
TRIM section
TRIM (
LEFT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length
)
)
TRIM will remove the extra spaces and show only the proper value. Now, we will get only the Names.
Like the above we need to do the same for other columns, but we can’t use the same formula. We need to modify slightly.
Email =
VAR String_Length = LEN(EmpDetails[EmpDetails])
RETURN
TRIM (
LEFT(
RIGHT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length*3
)
,String_Length
)
)
You need to use RIGHT function and multiply by 3 with the length then use the LEFT function.
Phone =
VAR String_Length = LEN(EmpDetails[EmpDetails])
RETURN
TRIM (
LEFT(
RIGHT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length*2
)
,String_Length
)
)
Department =
VAR String_Length = LEN(EmpDetails[EmpDetails])
RETURN
TRIM (
RIGHT(
SUBSTITUTE(EmpDetails[EmpDetails],
“|”, REPT(” “,String_Length))
,String_Length
)
)
Result is as like below.
First and Last columns will be easy. If you are working on 2nd column then you need to multiply by “Total columns -1”.
3rd Column “Total columns -2” and so on.
Still there could be other ways. Please share your thoughts.
Happy scripting.
About the Author