Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)
Proud to be a Super User!
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)
Proud to be a Super User!
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 "(".
Proud to be a Super User!
Does the script include how to handle <Blank column>?
I just realised there are few columns with empty data.
Cheers,
Kev
I am not clear about this . Could you please provide some sample data on this?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |