Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Replace values between square brackets in a Query

Hi, PowerBI newbie here but loving it!   In my Query I have a column where the values may have text in square brackets    eg: This is some text [and this is other text]   I would like to remove...
Need Help
  • ElenaN's avatar
    ElenaN
    7 years ago

    Hello,

     

    If you just want to replace the value in the brackets you can create a new column (Add Custom Column option in Power Query) and use this code:

     

    =Text.Replace([Column1], Text.BeforeDelimiter(Text.AfterDelimiter([Column1], "["), "]"), "")

    The code replaces whatever it is found between brackets with empty string. You can change that according to your needs.

     

    Regards,

    ElenaN

HotChilli's avatar
HotChilli
Community Champion
7 years ago

Depending on your sample data, for example if the extra clause is always the end of a string and never has anything after it, you could split the column in Power Query editor on the first occurrence of '[' and just use the first column.

 

Other forms of splitting and joining columns may work for you.

The 'true' algorithm for all cases would be : Find the position of '[' , Find the position of a subsequent ']' and remove the string between the two but i suspect that might be 'M' language that is further down your powerbi journey.