Text.RemoveBetweenDelimiters function for Power BI and Power Query

While Power Query has a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the optional 4th parameter. (“Text.RemoveBetweenDelimiters “- function)

The code for the Text.RemoveBetweenDelimiters -function


let func =
(TextToClean as text, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters) =>
let
removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else "",
Source = Text.From(TextToClean),
FirstSplit = List.Buffer( Text.Split(Source, StartDelimiter) ),
Custom2 = if List.First(FirstSplit) = "" then List.Skip(FirstSplit) else FirstSplit,
Custom1 = List.Transform(Custom2, each if Text.Contains(_, EndDelimiter) then Text.AfterDelimiter(_, EndDelimiter, 0) else _),
ListSelect = List.Select(Custom1, each _<>""),
TextCombine = Text.Combine(ListSelect, removeDelimiters)
in
TextCombine,
documentation = [
Documentation.Name = " Text.RemoveBetweenDelimiters ",
Documentation.Description = " Removes text between 2 delimiters. ",
Documentation.LongDescription = " Removes text between 2 delimiters. Option to remove the delimiters as well. Delimters are strings, so can contain multiple characters. ",
Documentation.Category = " Text ",
Documentation.Source = " www.TheBIccountant.com . https://wp.me/p6lgsG-2ak . ",
Documentation.Version = " 2.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-2ak . ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Text.RemoveBetweenDelimiters.pq

hosted with ❤ by GitHub

How to use

The Text.RemoveBetweenDelimiters – function has 3 mandatory parameters and an optional one:

  • Text itself (as text)
  • first delimiter (as text, can contain multiple characters)
  • second delimiter (as text, again can contain multiple characters)
  • optional string: Any value you enter here, will remove the delimiters themselves as well

Use cases

  • Delete HTML-tags like so:
    • Text_RemoveBetweenDelimiters(YourHtml, "<", ">", 1)
  • Delete multiline comments from DAX- or M-code (like I’ve done here for example).
    • Text_RemoveBetweenDelimiters(YourDAXorMQuery, "/*", "*/", "yes")

So once you enter any value into the fourth parameter, the delimiters themselves will be removed as well.

Enjoy and stay queryious 😉

Comments (9) Write a comment

  1. Pingback: Removing Text Between Delimiters in Power Query – Curated SQL

  2. Thank you for providing this, expect it to be very useful.

    Would it add too much complexity to add an additional optional parameter that identifies the number of first delimiters to ignore?

    Cheers
    Phil

    Reply

    • Hi Phil,
      this is definitely doable:

      (TextToClean as text, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters, optional SkipFirstXDelimiters as number) =>
      let
      /* Start of debug parameters
      TextToClean = Query1,
      StartDelimiter = "/",
      EndDelimiter = "      ./",
      RemoveDelimiters = null,
      SkipFirstXDelimiters = 1,
      End of debug parameters */
      removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else "",
      #"FirstPart´" = Text.BeforeDelimiter(TextToClean, StartDelimiter, {SkipFirstXDelimiters, RelativePosition.FromStart}),
      SecondPart = StartDelimiter & Text.AfterDelimiter(TextToClean, StartDelimiter, {SkipFirstXDelimiters, RelativePosition.FromStart}),
      FirstSplit = Text.Split(SecondPart, StartDelimiter),
      SecondSplit = List.Combine(List.Transform(FirstSplit, each Text.Split(_, EndDelimiter))),
      ListAlternate = List.Alternate(SecondSplit,1,1,1),
      ListSelect = List.Select(ListAlternate, each _<>""),
      TextCombine = Text.Combine(ListSelect, removeDelimiters)
      in
      TextCombine

      Cheers, Imke

      Reply

  3. Thanks very much for taking the time to provide that Imke, has been saved and ready for use.
    Cheers, Phil

    Reply

  4. Hi. Note that Power Query will not accept that the query/function has a fullstop in the name. If you change Text.BeforeDelimiter to TextBeforeDelimiter (i.e. no full-stop) everywhere in the code and the call to the function itself, it works fine.

    Reply

  5. The function works great! Unfortunately if there are rows with null values they return as error. A simple change to the function to handles the null values

    let func =
    (TextToClean as any, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters) =>
    let
    removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else “”,
    Source = if TextToClean = null then “”
    else Text.From(TextToClean),

    Reply

  6. I wonder if there is something I’m doing wrong? All this did was remove the delimiters, not the text between

    Reply

Leave a Reply