Forum Discussion

han_rj's avatar
han_rj
Helper IV
1 year ago
Solved

Replace Strings M query

 

Hi

 

I would like to achieve an optimal Mquery where In Side , Sun Side, Side should be replaced with Header annd others should  remain.

Issue faced using UI was that I had to create multiple replace transformation and when tried replacing Side all the rows were affected.

Thank You in advance for the help

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi han_rj,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    It looks like you want to replace selected values from rows with "Header" value and you want to use MQuery so that you don't have to create multiple transformation.

    Here is the MQuery to replace selected data rows from your desired data:

    Here "textColumn" is the name of the column in which you want to replace data with your desired data.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

    Best Regards,

    Hammad.

    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi han_rj,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    It looks like you want to replace selected values from rows with "Header" value and you want to use MQuery so that you don't have to create multiple transformation.

    Here is the MQuery to replace selected data rows from your desired data:

    Here "textColumn" is the name of the column in which you want to replace data with your desired data.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

    Best Regards,

    Hammad.

    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    This may work for you...

    Table.ReplaceValue(#"Changed Type", each [Strings], each if List.Contains({"In Side", "Sun Side", "Side"}, [Strings]) then "Header" else [Strings], Replacer.ReplaceValue, {"Strings"})
  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    I totally agree wit the solution of Anonymous .

    Here as copy/paste-able text:

     

    = Table.TransformColumns (Source,
    {{"TextColumn", each if List.Contains(ReplacementList, Text.Trim(_)) then "Header" else _, type text}}
)