Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I know that SUBSTITUTE function doesn't support wildcards (such a shame), however I need to combine it somehow with SEARCH function to achieve my goal./
So the problem is like this:
Existing column | New column |
This is Not_a_Product T001 | This is Not_a_Product |
This truly is Not_a_Product T002 | This truly is Not_a_Product |
This is Not_a_Product T003 | This is Not_a_Product |
Product T001 | Product T001 |
This truly is Not_a_Product TTTTTTT | This truly is Not_a_Product |
So if a "Not_a_Product" text is found, then replace everything AFTER this text with " "
Thanks
Solved! Go to Solution.
Finally did it!
Cleaned Column =
SWITCH (
TRUE (),
SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
'Table (2)'[Activity Name]
)
Finally did it!
Cleaned Column =
SWITCH (
TRUE (),
SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) <> 1000,
LEFT (
'Table (2)'[Activity Name],
LEN ( 'Table (2)'[Activity Name] )
- (
LEN ( 'Table (2)'[Activity Name] )
- SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 1, 1000 ) + 1
)
),
'Table (2)'[Activity Name]
)
Anyone please?
Some text Keyword1 T01 | Some text |
Some other text Keyword1 T02 | Some other text |
Some text Keyword1 | Some text |
Some other text Keyword1 T04 | Some other text |
Some text Keyword1 | Some text |
Some other text Keyword1 T06 | Some other text |
Some other text Keyword2 R0002 | Some other text |
Some text Keyword2 R0003 | Some text |
Someother text Keyword2 | Someother text |
Some text Keyword2 R0005 | Some text |
Sometext Keyword3 | Sometext |
So everything from the start of the Keywords needs to be removed
@Sab
Not clear, provide more clarity
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Provide example and expected output please.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Cleaned column =
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE ( TableName[ColumnName], "Keyword1", "" ),
"Keyword2",
""
),
"Keyword3",
""
),
"Keyword4",
""
),
"Keyword5",
""
),
"Keyword6",
""
)
So this is the solution I created to remove Keywords from the columns, but now the thing is that some of the keywords have additional text after them that needs to be removed, for example:
Keyword1 T01
Keyword1 T34
Keyword1 T22
So my formula Substitues only Keyword1 to Keyword6 with " ", and I need it to remove everything after these Keywords
@Sab
Add the following column:
New Column =
var _tlength = LEN("Not_a_Product")
var _tfoundat = SEARCH("Not_a_Product",TRIM(Table11[Existing column]),1,0)
var _result = LEFT(TRIM(Table11[Existing column]),_tfoundat+_tlength)
return
_result
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy
Thanks for your reply.
This works but the thing is now that I have multiple keywords with different length (so 'Not_a_Product' is just one of them)
Sorry I forget to mention this in the original message 😞
btw I need this to be done using DAX
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |