Forum Discussion

StephenF's avatar
StephenF
Responsive Resident
7 years ago
Solved

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    StephenF 

     

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

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

StephenF 

 

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

 

  • StephenF's avatar
    StephenF
    Responsive Resident
    7 years ago

    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 !