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.
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.
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.
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.
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(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(
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 (
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.
Hi Hariharan, I found really useful this information. I had no problems with the formula for the first column, but I’m having trouble with the other columns since I have my data changing from “4 columns” (PGVL-001-121-035) to even “1 column” (PGVL) since the data I want to visualize is rooted in different levels. Is there a way I could start just counting from the LEFT instead of the right? I need to separte this column in the 4 different levels I have. Help would be really appreciated, thanks in advance.
Wow! it really helped me 🙂 thank you so much for the boost.
But my situation is my string value is not standard , my column is a path of SSRS report.
For example :
Column Name : Path
Value1 : /FolderABC/FolderEFG/FolderGHI/ReportName
Value2 : /FolderXYZ/ReportName
Value3 : /FolderJKL/FolderFRT/ReportName
If you can see all the values are very random and I want to split the value as you did into individual columns dynamically.
Can you please help.
Thank you
I was looking for similar solution but “New Column” is inactive for my direct query. Any idea ?
About the Author