DAX Queries to split columns

[BigKev]

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

[ryan_mayu · Accepted solution]

@BigKev 

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)

[BigKev]

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

[ryan_mayu]

@BigKev 

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 "(".

[BigKev]

Does the script include how to handle <Blank column>?

I just realised there are few columns with empty data.

Cheers,

Kev

[ryan_mayu]

@BigKev 

I am not clear about this . Could you please provide some sample data on this?