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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
StephenF
Responsive Resident
Responsive Resident

Single Wildcard with replace for text cleansing

I wish to do as you would in excel's search replace:

 

Replace ~(ignore double quotes here)

 

" S1*"     - (S1 followed by a wildcard

 

to be replaced with "" ie no text

 

EG

 

"Mario S1 E1" Would be replaced with "Mario".

 

asterisk doesnt seem to work in replace values in the query editor however.

 

Any way to do the same?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@StephenF 

 

You can use Extract Text Before Delimiter and specify "S1" there

 

Mario.png


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @StephenF ,

 

You can add a column with condition to replace value, please take a look at following query.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each try if Text.Split([Text]," S1")<>null then Text.Split([Text]," S1"){0} else [Text] otherwise [Text])

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sysxXCDZUcDVUitWJVgpJLS4Bco3AHLgkRM7RyRXESS1CkdQyMlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each try if Text.Split([Text]," S1")<>null then Text.Split([Text]," S1"){0} else [Text] otherwise [Text])
in
    #"Added Custom"

5.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@StephenF 

 

You can use Extract Text Before Delimiter and specify "S1" there

 

Mario.png


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad  Strictly this works, but if i chain a bunch of these it only picks up the last one.

The procession doesnt save the progress of the transformation on the string after each step for this function which is a bit unusual.

eg:

 

    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S1"), type text}}),
    #"Extracted Text Before Delimiter2" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S3"), type text}}),
    #"Extracted Text Before Delimiter3" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S4"), type text}}),
    #"Extracted Text Before Delimiter4" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S5"), type text}}),

 

EDIT: Oh right I need to change  #"Replaced Value" to point to previous row also on each row for it to work !

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.