Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sab
Helper V
Helper V

SUBSTITUTE text found using wildcards

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 columnNew column
This is Not_a_Product T001This is Not_a_Product
This truly is Not_a_Product T002This truly is Not_a_Product
This is Not_a_Product T003This is Not_a_Product
Product T001Product T001
This truly is Not_a_Product TTTTTTTThis truly is Not_a_Product

 

So if a "Not_a_Product" text is found, then replace everything AFTER this text with " "

 

Thanks

1 ACCEPTED SOLUTION
Sab
Helper V
Helper V

Finally did it!

 

Cleaned Column =
SWITCH (
    TRUE (),
    SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    'Table (2)'[Activity Name]
)

View solution in original post

9 REPLIES 9
Sab
Helper V
Helper V

Finally did it!

 

Cleaned Column =
SWITCH (
    TRUE (),
    SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword1", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword2", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword3", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword4", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword5", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 11000 ) <> 1000,
        LEFT (
            'Table (2)'[Activity Name],
            LEN ( 'Table (2)'[Activity Name] )
                - (
                    LEN ( 'Table (2)'[Activity Name] )
                        SEARCH ( "Keyword6", 'Table (2)'[Activity Name], 11000 ) + 1
                )
        ),
    'Table (2)'[Activity Name]
)

Sab
Helper V
Helper V

Anyone please?

Sab
Helper V
Helper V

Some text Keyword1 T01Some text
Some other text Keyword1 T02Some other text
Some text Keyword1Some text
Some other text Keyword1 T04Some other text
Some text Keyword1Some text
Some  other text Keyword1 T06Some  other text
Some other text Keyword2 R0002Some other text
Some text Keyword2 R0003Some text
Someother text Keyword2Someother text
Some text Keyword2 R0005Some text
Sometext Keyword3Sometext

 

So everything from the start of the Keywords needs to be removed

Fowmy
Super User
Super User

@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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
Super User

@Sab 

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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


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

Fowmy
Super User
Super User

@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

 

Fowmy_0-1606154738692.png

________________________

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 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


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 😞

Sab
Helper V
Helper V

btw I need this to be done using DAX

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.