Text.RemoveBetweenDelimiters function for Power BI and Power Query

While there is 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..

The code

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: If any value is entered here, the delimiters themselves will be eliminated as well

Use cases

  • Delete HTML-tags:
    • 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 (4) 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

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz