none
Replace Value/Text with pattern/wildcard RRS feed

  • Question

  • Hi, 

    I've seen many questions about replacing value/text with wildcard. But all solutions I've seen unfortunately have been not for wildcards per se. It's been looking for whole explicit words anywhere in the field value. Which I'm afraid doesn't work for me. Or I just couldn't get them to work properly...

    What I need is to replace all instances that starts with certain characters, and end with certain character and the pattern need to be at the start of the value.

    My values can look like this (just a few examples of the variety):
    C400/OE-AD304KDS/XE
    C4R3O/REDACK392E
    XM-RAC33A
    VAM-OKF38F39VC4ER/EE

    The only part I need replaced, or rather deleted, is the first part of the ones starting with C4 followed by unknown amount of characters and ends with an /.
    So the examples above would've been:
    OE-AD304KDS/XE (C400/ is removed since it's a match of the criteria that the value starts with C4*/)
    REDACK392E (C4R30/ is removed since it's a match of the criteria that the value starts with C4*/)
    XM-RAC43A (untouched because it didn't start with C4*/)
    VAM-OKF38F39VC4ER/EE (untouched because it didn't start with C4*/ even though it includes C4*/ at the end)

    Basically this is what I want:
    Table.ReplaceValue(#"Previous Step","C4*/","",Replacer.ReplaceText,{"Column to replace values in"}

     But that would explicitly search for "C4*/" and wont e.g. match "C4R30/" 


    Any Power Query guru who can guide me to accomplish this?

    Thank you very much for your time and help.



    • Edited by alekksh Tuesday, February 25, 2020 9:16 AM Clarification
    Tuesday, February 25, 2020 7:54 AM

Answers

  • OK, so

    let
        Source = Table.FromRecords({[col1="C400/OE-AD304KDS/XE"],[col1="C4R3O/REDACK392E"],[col1="XM-RAC33A"],[col1="VAM-OKF38F39VC4ER/EE"]},
            type table [col1 = text]
        ),
        RemovedTextIf = Table.TransformColumns(Source,
            {"col1", each
                if not Text.StartsWith(_,"C4",Comparer.OrdinalIgnoreCase) then _
                else Text.Middle(_,Text.PositionOf(_,"/",Occurrence.First) +1),
                type text
            }
        )
    in
        RemovedTextIf
    Results in:

    • Marked as answer by alekksh Tuesday, February 25, 2020 12:36 PM
    Tuesday, February 25, 2020 11:26 AM

All replies

  • Hi

    I had something for you until you edited your post. And your edit (that would explicitly search for "C4*/" and wont e.g. match "C4R30/") raises a question: Power Query or not how do you envision this to work?

    Tuesday, February 25, 2020 9:31 AM
  • Many thanks for your reply!
    I apologize, my try to clarify apparently mudded the water even more.. :)

    What I mean with "But that would explicitly search for "C4*/" and wont e.g. match "C4R30/" " is that if I at this moment would run 
    Table.ReplaceValue(#"Previous Step","C4*/","",Replacer.ReplaceText,{"Column to replace values in"} 

    then PowerQuery will try to match "C4*/" not eg. "C4R30/" because "C4R30/" isn't "C4*/" - i.e. Powerquery will look for the actual asterisk and not treat it as a wildcard for any number of characters. Sorry for the inconvenience. And thanks for spending your time helping me...

    • Edited by alekksh Tuesday, February 25, 2020 10:16 AM
    Tuesday, February 25, 2020 10:13 AM
  • my try to clarify apparently mudded the water even more.. :) Never easy in writing :(

    PowerQuery will try to match "C4*/" not eg. "C4R30/" because "C4R30/" isn't "C4*/" - i.e. Powerquery will look for the actual asterisk and not treat it as a wildcard for any number of characters
    I think I understand but that would be different from what you initially asked (or I'm totally lost):

    From:                                         To:
    C400/OE-AD304KDS/XE              OE-AD304KDS/XE
    C4R3O/REDACK392E                   REDACK392E
    XM-RAC33A                                XM-RAC43A
    VAM-OKF38F39VC4ER/EE            VAM-OKF38F39VC4ER/EE

    Not sure it's realistic we keep exchanging to try clarifying the ambition. Suggestion: upload somewhere (OneDrive, Google Drive…) a workbook with a few++ representative text values as they are + what you expect in the next column and post the link to the file in your next reply

    Hope this makes sense


    • Edited by Lz._ Tuesday, February 25, 2020 10:35 AM
    Tuesday, February 25, 2020 10:35 AM
  • No, you're correct, 
    I want the result like this:
    From:                                         To:
    C400/OE-AD304KDS/XE              OE-AD304KDS/XE
    C4R3O/REDACK392E                   REDACK392E
    XM-RAC43A                                XM-RAC43A
    VAM-OKF38F39VC4ER/EE           VAM-OKF38F39VC4ER/EE

    And it's what I initially asked, to replace a pattern with nothing (ie deleting the matched string from the value).
    In the example in above, the two first rows are omitted of the matched pattern C400/ is a match of the pattern C4*/, C4R3O is also a match of the pattern C4*/.
    However the two last rows do not match the pattern because the potential match is at the end of the value which I want to keep. I've underlined the matched pattern of C4*/ in the examples above.

    Tuesday, February 25, 2020 11:10 AM
  • OK, so

    let
        Source = Table.FromRecords({[col1="C400/OE-AD304KDS/XE"],[col1="C4R3O/REDACK392E"],[col1="XM-RAC33A"],[col1="VAM-OKF38F39VC4ER/EE"]},
            type table [col1 = text]
        ),
        RemovedTextIf = Table.TransformColumns(Source,
            {"col1", each
                if not Text.StartsWith(_,"C4",Comparer.OrdinalIgnoreCase) then _
                else Text.Middle(_,Text.PositionOf(_,"/",Occurrence.First) +1),
                type text
            }
        )
    in
        RemovedTextIf
    Results in:

    • Marked as answer by alekksh Tuesday, February 25, 2020 12:36 PM
    Tuesday, February 25, 2020 11:26 AM
  • Life saver! Works like a charm.

    Thanks for hearing me out and helping me with this. Gold's worth. 

    Cheers.
    Tuesday, February 25, 2020 12:37 PM
  • You're welcome. Glad I could help & Thanks for posting back

    For more complex scenario see Imke's Multiple replacements or translations in Power BI and Power Query article

    Nice day..

    Tuesday, February 25, 2020 12:47 PM