Forum Discussion
DAX Queries to split columns
Hi All,
Data From Raw Column | The Column that I want to Achieve | ||
Skill Table | Skill Name | Skill Level | |
10 - .NET Programming Language (Advanced) | .NET Programming Language | Advanced | |
10 - AWS Lambda Administration (Expert) | AWS Lambda Administration | Expert | |
10 - Application Design (Beginner) | Application Design | Beginner | |
11 - Oracle BI Application (OBIA) Supply Chain and Order Management Analytics (Intermediate) | Oracle BI Application (OBIA) Supply Chain and Order Management Analytics | Intermediate | |
10 - Amazon Web Services (AWS) Security (Advanced) | Amazon Web Services (AWS) Security | Advanced | |
10 - Change Management (Beginner) | Change Management | Beginner |
I have this raw column on the left side, and I want to split the column into two (on the right). I have tried using Mid() and Right(). However, the result is not good because the number of characters varies.
Split columns on Power query is not an option, because this is a ‘summarise table’. Power Query only allows you to customise the data source Table. I am also a bit reluctant to split it in the source table. Because it is going to create an additional 120 columns, and it will impact dashboard performance.
Does anyone have any workaround or idea on how to achieve this? Does any DAX queries that I could use for the split?
Cheers,
Kev
please try below DAX to create columns
Column = VAR P1 = FIND("-",Sheet5[Skill Table]) VAR num=len(Sheet5[Skill Table])-LEN(SUBSTITUTE(Sheet5[Skill Table],"(","")) VAR new = SUBSTITUTE(Sheet5[Skill Table],"(","@",num ) VAR P2= FIND("@",new) return mid(new,P1+1,p2-p1-2) Column1 = VAR num=len(Sheet5[Skill Table])-LEN(SUBSTITUTE(Sheet5[Skill Table],"(","")) VAR new = SUBSTITUTE(Sheet5[Skill Table],"(","@",num ) VAR P1= FIND("@",new) return mid(new,P1+1,len(Sheet5[Skill Table])-p1-1)
5 Replies
please try below DAX to create columns
Column = VAR P1 = FIND("-",Sheet5[Skill Table]) VAR num=len(Sheet5[Skill Table])-LEN(SUBSTITUTE(Sheet5[Skill Table],"(","")) VAR new = SUBSTITUTE(Sheet5[Skill Table],"(","@",num ) VAR P2= FIND("@",new) return mid(new,P1+1,p2-p1-2) Column1 = VAR num=len(Sheet5[Skill Table])-LEN(SUBSTITUTE(Sheet5[Skill Table],"(","")) VAR new = SUBSTITUTE(Sheet5[Skill Table],"(","@",num ) VAR P1= FIND("@",new) return mid(new,P1+1,len(Sheet5[Skill Table])-p1-1)Thanks for the prompt response Ryan.
When I tried to the DAX code, it returns me with this error:
"The search Text provided to function 'FIND' could not be found in the given text."
Do you know what might be the cause?
Cheers,
Kev
I guess the bracket issue?
（
(
Branket may be different in the different input.
Maybe you can try the DAX step by step. (return each var to see which part of the DAX causes this problem)
What's more, I created the DAX based on your sample data. Not sure if all column got "(".
If not, then find function does not work becuase can't find "(".
