locked
Search & Replace RRS feed

  • Question

  • Hi There,

    I have a table with 8k+ rows. One on the columns contains text containing the characters [ and ]. For example:

    Col a Colb

    1 This is some text with an [interesting word] in it

    2 This is some text with an [interesting word] <-- different text, "in it" is removed

    3 This is some text

    4 This is some text with an [interesting word] in it

    What I want is a way to extract the 'interesting word' from the closed brackets [ ] and use this as a replacement of the contents of the text. So the result should be:

    1 interesting word

    2 interesting word

    3 ""

    4 with interesting word

    Is this possible with PowerQuery? If so, how should this be done?

    Thanks in advance.

    /Me

    Thursday, February 8, 2018 1:46 PM

Answers

  • Hi Me,

    I was following along until I got to the fourth example.

    1. Replace the text with the text in square brackets - no problem here.
    2. Replace the text with the text in square brackets - no problem here.
    3. There are no square brackets, so replace the text with an empty string - no problem here.
    4. The text in the square bracket ("interesting word"), does not just replace the text, but also includes the word "with." What is the logic used in this case?

    In general, if we want to extract text between delimiters, we can use the function Text.BetweenDelimiters. For example Text.BetweenDelimiters("This is some text with an [interesting word] in it", "[", "]") would return interesting word. If the delimiters are absent from the text (example 3), the function returns an empty string.

    Thursday, February 8, 2018 5:49 PM
  • Hi Colin,

    thanks for the reply. This is exactly what I want. Find any text between delimiters

    When I examined your option I noticed the Extract option from the Menu. Turns out it has the exact option you mentioned. It generates the following M code:

    = Table.TransformColumns(#"Reordered Columns", {{"<ColumnName>", each Text.BetweenDelimiters(_, "[", "]", 0, 0), type text}})

    Brilliant! Love PQ, but learing it!

    /Me


    Thursday, February 8, 2018 6:00 PM

All replies

  • Hi Me,

    I was following along until I got to the fourth example.

    1. Replace the text with the text in square brackets - no problem here.
    2. Replace the text with the text in square brackets - no problem here.
    3. There are no square brackets, so replace the text with an empty string - no problem here.
    4. The text in the square bracket ("interesting word"), does not just replace the text, but also includes the word "with." What is the logic used in this case?

    In general, if we want to extract text between delimiters, we can use the function Text.BetweenDelimiters. For example Text.BetweenDelimiters("This is some text with an [interesting word] in it", "[", "]") would return interesting word. If the delimiters are absent from the text (example 3), the function returns an empty string.

    Thursday, February 8, 2018 5:49 PM
  • Hi Colin,

    thanks for the reply. This is exactly what I want. Find any text between delimiters

    When I examined your option I noticed the Extract option from the Menu. Turns out it has the exact option you mentioned. It generates the following M code:

    = Table.TransformColumns(#"Reordered Columns", {{"<ColumnName>", each Text.BetweenDelimiters(_, "[", "]", 0, 0), type text}})

    Brilliant! Love PQ, but learing it!

    /Me


    Thursday, February 8, 2018 6:00 PM