Text.RemoveBetweenDelimiters function for Power BI and Power Query

by

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

https://gist.github.com/ImkeF/b025cac646c90de394d9d96b6061b12b

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 😉

9 thoughts on “Text.RemoveBetweenDelimiters function for Power BI and Power Query”

  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

  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 comment