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.
I am fetching data from SharePoint list.
Requirement:
there is a string, from which I need to find two fix words (AD and PC), and send the finding into a new column.
Solved! Go to Solution.
Hi @cj_sh ,
You could add if() function to return the results you want.
if
Text.Contains([Column1],"PC")=true
then
[Column1]
else
"Your other result"
Hi @cj_sh ,
The easiest way to achieve this is Conditional Columns in Power Query.
Please Use Operator as contains or Equal according to your other options.
Please use the attached picture as reference.
Best Regards,
Gaurav Raj Singh
you can do it on power query or dax
in power query, you can create a conditional column and put your condition there.
in dax, you can use containsstring function to find your desired words and use if statement
@mussaenda Thanks, I don't know why my query editor is not showing ContainsString function among the choices.
but I am trying Text.Contains([ColumnName],"AD"), and it is giving me True/False whereever AD in the string.
Now I am getting challenge in value AD in the column instead of True.
Can you help me in that..
Hi @cj_sh ,
The language used in Power Query is M while that in the designer is DAX that's why you can't use CONTAINSTRING in the query editor. For your requirement, you can try this in DAX:
New Column =
//will return blank if both criteria are not met
IF (
CONTAINSSTRING ( Table[Column], "AD" ) && CONTAINSSTRING ( Table[Column], "PC" ),
Table[Column]
)
EDIT:
M is case-sensitive. That's why you are getting a TRUE/FALSE result instead of just either. You can wrap your column first in Text.Upper before using Text.Contains so it would be something like:
Text.Contains ( Text.Lower ( [Column] ) )
Proud to be a Super User!
Hi @danextian ,
I think in this situation OR will be the right condition, not AND.
So in DAX use the below formula,
New Column =
//will return blank if both criteria are not met
IF (
CONTAINSSTRING ( Table[Column], "AD" ) || CONTAINSSTRING ( Table[Column], "PC" ),
Table[Column]
)
Hi @cj_sh ,
You could add if() function to return the results you want.
if
Text.Contains([Column1],"PC")=true
then
[Column1]
else
"Your other result"
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |