Text Functions in Power Query M (150+ Examples)

Text Functions in Power Query thumbnails

Text functions are foundational in your Power Query journey. They allow you to analyze and manipulate text values. And once you master them you will surely enjoy their powers. This post gives countless examples of how you can use Text Functions in Power Query. Its aim is not to describe all intricacies, but more to give you ready examples to start using.

1. Insert Text

In Power Query you can insert text with different functions. The Text.Insert function allows you to add a text value into an existing text value at a specified position. This position starts at an index of 0.

= Text.Insert( "Fish", 0, "Big " ) // Returns "Big Fish"
= Text.Insert( "Arc", 3, "h")       // Returns "Arch"
// Combine functions to add text at the end, returns "ABC"
= Text.Insert( "Ab", Text.Length( "Ab") , "c") 

There are times where you want your text value to have a specific length. To address that, you can insert a value at the start or end of a string. The functions Text.Padstart and Text.PadEnd can perform that.

Both text functions have three arguments. First a text value, then the desired number of characters for the new string, and an optional character used for padding.

The default padding character is a space. It is used when the third argument is left empty. Yet you can provide the third argument with the padding character you desire. A typical use is to add leading zeros to a value.

= Text.PadStart( "Gorilla", 8   ) // Returns " Gorilla"
= Text.PadStart( "5440", 7, "0" ) // Returns "0005440"
= Text.PadStart( "544",  7, "0" ) // Returns "0000544"
= Text.PadStart( "54",   7, "0" ) // Returns "0000054" 

= Text.PadEnd( "Gorilla", 8   )  // Returns "Gorilla "
= Text.PadEnd( "5440", 7, "0" )  // Returns "5440000"
= Text.PadEnd( "544",  7, "0" )  // Returns "5440000"
= Text.PadEnd( "54",   7, "0" )  // Returns "5400000" 

2. Remove Text

The Text.Remove function allows you to remove all occurrences of a character. You can remove letters, numbers or any other character. The function has two arguments.

The first argument takes a text value, the second argument requires you to input one or more characters to remove input as single-character strings. You can enter this as a single text value or as a comma separated list with single-character strings.

// You can remove a single character or multiple.
= Text.Remove( "Rick de Groot", "o" )          // returns "Rick de grt"
= Text.Remove( "bigorilla", { "i", "e", "o" } ) // returns "bgrlla"

// The Text.Remove function is capital sensitive.
= Text.Remove( "Rick", "R" ) // Returns "ick"
= Text.Remove( "Rick", "r" ) // Returns "Rick"

// And accepts numbers, letters but also special characters.
= Text.Remove( "A/B_C", { "/", "_" } ) // Returns "ABC"

// You can also use a shortcut to generate the values to remove.
= Text.Remove( "power-BI99", { "A".."Z" } )           // "power-99"
= Text.Remove( "power-BI99", { "a".."z" } )           // "-BI99"
= Text.Remove( "power-BI99", { "a".."z", "A".."Z" } ) // "-99"
= Text.Remove( "power-BI99", { "0".."9" } )           // "power-BI"

The previous examples remove all provided characters from a string. There may be cases where you want to remove the provided characters at the start or end of a string. You can use Text.TrimStart and Text.TrimEnd for those cases.

= Text.TrimStart( "***gorilla***", "*" )       // Returns "gorilla***"
= Text.TrimStart( "* * gorilla", {" ", "*"} ) // Returns  "gorilla"

= Text.TrimEnd( "***gorilla***", "*" )  // Returns "***gorilla"
= Text.TrimEnd( "!845pqr", {"a".."z"} ) // Returns "!845"

Whereas the Text.RemoveRange function sounds very similar to Text.Remove, it does something completely different. The function allows you to provide an offset and a number of characters to remove. It then removes that number of characters starting from the offset position.

= Text.RemoveRange( "9-AR-C-85", 0, 5 ) // Returns "C-85"
= Text.RemoveRange( "9-AR-C-85", 3, 3 ) // Returns "9-A-85"

3. Extract Text

The next category of text functions focuses on extracting values from strings.

One of my favorite functions is the Text.Select function. You can instruct the function to keep all occurrences of one or more given characters. And with that it is the exact opposite of the Text.Remove function.

The Text.Select function has two arguments. First it needs a text value, and the second arguments contains the characters to keep.

= Text.Select( "jUxL2jE98Z", { "0" .. "9" } )    // Returns "298"
= Text.Select( "jUxL2jE98Z", { "a" .. "z" } )    // Returns "jxj"
= Text.Select( "jUxL2jE98Z", { "A" .. "Z" } )    // Returns "ULEZ"
= Text.Select( "jUxL2jE98Z", { "x", "E", "9" } ) // Returns "xE9"

The Text.Start function extracts the first characters of a string, whereas the Text.End function extracts the last characters of a text value. Both functions take a text value as first argument and require the number of characters to extract as second argument.

= Text.Start( "Life is good", 4 ) // Returns "Life"
= Text.Start( "Life is good", 7 ) // Returns "Life is"

= Text.End( "Life is good", 4 ) // Returns "good"
= Text.End( "Life is good", 7 ) // Returns "is good"

You may sometimes need to return values at a specific position in a string. You can return a single character using the Text.At function. It requires a text value as first argument and the offset position of your desired character as second argument.

To return multiple values you can use the Text.Range function in Power Query. It takes a text value as first argument and offset position as second. You can optionally provide the third argument to provide the number of characters to return. If this argument is left out, the function returns all characters starting from the offset position.

// Text.At always returns a single character
= Text.At( "Run@", 3 )     // Returns "@"
= Text.At( " - - - ", 1 ) // Returns "-"

// Text.Range can return a single character
= Text.Range( "Run@", 3 )    // Returns "@"
= Text.Range( "Run@", 3, 1 ) // Returns "@"
= Text.Range( "Run@", 5, 1 ) // Returns error: offset is out of range

// Or provides you with a range of values.
= Text.Range( " - - - ", 1 )    // Returns "- - - "
= Text.Range( " - - - ", 1, 3 ) // Returns "- -"

= Text.Range( "I am Groot", 2 )    // Returns "am Groot"
= Text.Range( "I am Groot", 2, 2 ) // Returns "am"

The Text.Middle function works identical to the Text.Range function. With one exception. When the Text.Middle function is out of range, it still returns a value. The Text.Range function however throws the error: “Expression.Error: The ‘count’ argument is out of range.

= Text.Middle( "Hello World", 6, 10 ) // Returns World
= Text.Range( "Hello World", 6, 10 )  // Returns Error

The functions so far required a specific offset or number of characters to retrieve. Another set of functions extract values based on delimiters.

The function Text.AfterDelimiter extracts all text after your specified delimiter, whereas Text.BeforeDelimiter extracts everything before the delimiter. Both functions have 2 mandatory arguments and one optional argument.

The first argument requires a text value and the second argument takes the delimiter to find. By default both functions will look for the first delimiter they find.

If you don’t want to look for the first delimiter, you can use the third optional argument to indicate the number delimiters to skip before returning a value. You can even indicate from which side to skip your delimiters by using RelativePosition.FromStart and RelativePosition.FromEnd

= Text.BeforeDelimiter( "x-87-v", "-" )   // Returns "x"
= Text.BeforeDelimiter( "x-87-v", "-", 0 ) // Returns "x"
= Text.BeforeDelimiter( "x-87-v", "-", 1 ) // Returns "x-87"

// You can indicate from which side to search for the delimiter
// by using RelativePosition.FromStart and RelativePosition.FromEnd
// The functions by default use RelativePosition.FromStart

// Below formulas return: 1) "x", 2) "x", 3 ) "x-87", 4) "x"
1 = Text.BeforeDelimiter( "x-87-v", "-",  0 )
2 = Text.BeforeDelimiter( "x-87-v", "-", { 0, RelativePosition.FromStart } )
3 = Text.BeforeDelimiter( "x-87-v", "-", { 0, RelativePosition.FromEnd } )
4 = Text.BeforeDelimiter( "x-87-v", "-", { 1, RelativePosition.FromEnd } )

// Text.AfterDelimiter works with similar arguments.
= Text.AfterDelimiter( "x-87-v", "-" )    // Returns "87-v"
= Text.AfterDelimiter( "x-87-v", "-", 0 ) // Returns "87-v"
= Text.AfterDelimiter( "x-87-v", "-", 1 ) // Returns "v"
 
// Below formulas return: 5) "87-v", 6) "87-v", 7) "v", 8) "87-v"
5 = Text.AfterDelimiter( "x-87-v", "-",  0 )
6 = Text.AfterDelimiter( "x-87-v", "-", { 0, RelativePosition.FromStart } )
7 = Text.AfterDelimiter( "x-87-v", "-", { 0, RelativePosition.FromEnd } )
8 = Text.AfterDelimiter( "x-87-v", "-", { 1, RelativePosition.FromEnd } )

The shown examples look at text before or after a delimiter. Yet one text function is still missing. The Text.BetweenDelimiters function extracts text between a specified start and end delimiter.

Just like its two siblings this function accepts a start and end index to indicate the number of values to skip. And you can turn both into a list index to also indicate whether you should skip values at the end or start of the input.

= Text.BetweenDelimiters( "x-8-v-0", "-", "-" ) // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "v" ) // Returns "8-"
= Text.BetweenDelimiters( "x-8-v-0", "-", "0" ) // Returns "8-v-"

= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 1 ) //Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 2 ) // Returns "0"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 1, 0 ) // Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 1, 1 ) // Returns "v-0"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 0, 1 ) // Returns "8-v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 0, 2 ) // Returns "8-v-0"

// Below three expression are identical. The function uses 
// RelativePosition.FromStart and an index of 0 by default.
= Text.BetweenDelimiters( "x-8-v-0", "-", "-" )       // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 0, 0 ) // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 
                     { 0, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "8"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 1, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 2, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "0"
// Once you reach the end of the input increasing the index has no impact
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 2, RelativePosition.FromStart }, 
                     { 1, RelativePosition.FromStart }) // Returns "0"

// The function looks for the second "-", and then from there looks back 
// to the start of the input value
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 1, RelativePosition.FromStart }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x-8"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 2, RelativePosition.FromStart }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x-8-v"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 2, RelativePosition.FromEnd }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x"

4. Transform Text

This chapter focuses on text functions that help in transforming and cleaning text values. Some are relatively easy with a one or two arguments. Let’s have a look at how you can use Text.Lower, Text.Upper, Text.Proper, Text.Trim, Text.Clean, Text.Reverse, Text.Repeat and Text.Combine.

= Text.Lower( "abcDEF" ) // Returns "abcdef"
= Text.Upper( "abcDEF" ) // Returns "ABCDEF"

// Capitalizes the first letter of each word
= Text.Proper( "abcDEF" )               // Returns "Abcdef"
= Text.Proper( "i like cats and dogs" ) // Returns "I Like Cats And Dogs"

// Removes control characters from a string
= Text.Clean( "R#(lf)i#(cr)ck" ) // Returns "Rick

You can also focus on removing spaces, reversing text or combining them.

// Removes leading & trailing characters, white space by default
= Text.Trim( "    gorilla    " )            // Returns "gorilla"
= Text.Trim( "    gorilla    ", " " )       // Returns "gorilla"
= Text.Trim( "- -gorilla- -", "-" )         // Returns " -gorilla- "
= Text.Trim( "- -gorilla- -", "-" )         // Returns " -gorilla- "
= Text.Trim( "- -gorilla- - ", { "-", " " } ) // Returns "gorilla"

= Text.Reverse( "gorilla" ) // Returns "allirog"
= Text.Repeat( "abc", 3 )    // Returns "abcabcabc"

// The second argument allows you to input an optional separator
= Text.Combine( { "abc", "123" } )          // Returns "abc123"
= Text.Combine( { "abc", "123" }, "-" )     // Returns "abc-123
= Text.Combine( { "ab", "12", "." }, "-" ) // Returns "ab-12-."

A unique text function to combine and format your texts is Text.Format. As arguments the function first takes a format string, then an argument from a list or record and optionally a culture code. You can achieve similar results with the Text.Combine function. Yet a benefit of this function is that you can provide a culture code.

= Text.Format( "The #[Subject] wants a #[Food].", 
                [ Subject = "Gorilla", Food = "Banana" ]  )
// Returns values from a record: "The Gorilla wants a Banana."

= Text.Format( "#{0}, #{1} and #{2} are friends.", 
               { "Rick", "Ben", "Mila" } )
// Returns values from a list: "Rick, Ben and Mila are friends."

// Adding a culture code allows you to force local formatting

= Text.Format( "Please pay us €#{0} before #{1}.", 
               { 49.99, #date(2022, 5, 1 ) }, "EN-us" )
// Returns "Please pay us €49.99 before 5/1/2022."

= Text.Format( "Please pay us €#{0} before #{1}.", 
               { 49.99, #date(2022, 5, 1 ) }, "NL-nl" )
// Returns "Please pay us €49,99 before 1-5-2022."

The opposite of combining values is splitting them. Power Query has the text functions Text.Split and Text.SplitAny to split values. As arguments the functions both take a text value and then one or more separators as second argument. After execution the function returns a list.

= Text.Split( "06-18-18-22", "1" )    // Returns { "06-", "8-", "8-22" }
= Text.Split( "06-18-18-22", "-" )    // Returns { "06", "18", "18", "22" }
= Text.SplitAny( "06-18-18-22", "-" ) // Returns { "06", "18", "18", "22" }

// For multiple separators, Text.SplitAny does not require a list.
= Text.SplitAny( "6|8|8-2", "-|" )     // Returns { "6", "8", "8", "2" }
= Text.SplitAny( "RickDeGroot", "er" ) // Returns { "RickD", "G", "oot" }

Very similar is the Text.ToList function. The Text.ToList function takes a string and returns a list containing all single-character text values of this string.

=Text.ToList( "Rick" ) // Returns { "R", "i", "c", "k" }

Another category of functions focusses on replacing values. Power Query offers the functions Text.Replace and Text.ReplaceRange that both have their own approach to this. Text.Replace replaces all occurrences of a specified text value and replaces these with a new text value. The replacement is case senstive.

= Text.Replace( "I enjoy sunny days", "enjoy", "love" ) 
// Returns "I love sunny days"

= Text.Replace( "my friend goes to my event", "my", "his" ) 
// Returns "his friend goes to his event"

The Text.ReplaceRange removes a given amount of characters and replaces these with the provided text value. It works very similar to the Text.RemoveRange function, with the difference that it allows you to replace the removed range with a provided value.

As arguments it takes a text value, an offset to start the replacement, the number of values to replace and lastly ends with the new text value.

= Text.ReplaceRange( "RickVanGroot", 4, 3, "De" ) // Returns "RickDeGroot"
= Text.ReplaceRange( "RickVanGroot", 4, 3, "" ) // Returns "RickGroot"

5. Informational Functions

Informational functions are a category of text functions that analyze text values for certain criteria. An important function is Text.Contains. The Text.Contains function checks whether a text value contains a string. By default it does this case sensitive. However, you can provide the function with a comparer to alter the behavior and make it case-insensitive comparison.

= Text.Contains( "Red roses", "Red" ) // Returns true
= Text.Contains( "Red roses", "red" ) // Returns false
= Text.Contains( "Red roses", "red",
         Comparer.Ordinal )           // Returns false
= Text.Contains( "Red roses", "red",
         Comparer.OrdinalIgnoreCase ) // Returns true

Then there are functions that check whether a value starts or ends with a given string. Power Query uses Text.StartWith and Text.EndsWith for that.

= Text.StartsWith( "I got the Power", "I got" ) // Returns true
= Text.StartsWith( "I got the Power", "i got" ) // Returns false
= Text.StartsWith( "I got the Power", "i got", 
               Comparer.Ordinal )               // Returns false
= Text.StartsWith( "I got the Power", "i got", 
               Comparer.OrdinalIgnoreCase )     // Returns true

= Text.EndsWith( "Biking to home", "home" ) // Returns true
= Text.EndsWith( "Biking to home", "HOME" ) // Returns false
= Text.EndsWith( "Biking to home", "HOME", 
               Comparer.Ordinal)            // Returns false
= Text.EndsWith( "Biking to home", "HOME", 
               Comparer.OrdinalIgnoreCase ) // Returns false

Other functions can return valuable information about a value. The Text.Length returns the length of a text value. You can use this information again in other functions to for example make sure each character has the same length.

= Text.Length( "Rick" )      // Returns 4
= Text.Length( "BIGorilla" ) // Returns 9

In its most basic form, the Text.PositionOf function returns the first position of a given substring in a text value. The first argument takes the text value and the second requires the substring. When a substring can’t be found, the function returns -1.

By default the function returns the position of the first occurrence of the substring. However, you can use the occurrence parameter in the optional third argument to change this behavior. You can use:

Occurrence.First (0) – returns the position of first occurrence of the searched value
Occurrence.Last (1) – returns the position of last occurrence of the searched value
Occurrence.All (2) – returns a list of positions of all occurrences of the searched value

= Text.PositionOf( "We all want pasta", "a" ) // Returns 3

// An index number determines which position of the value to return

// The first occurrence of a value is returned by default, returns 3
= Text.PositionOf( "We all want pasta", "a", Occurrence.First ) 
= Text.PositionOf( "We all want pasta", "a", 0 ) 

// Occurrence.Last returns the last occurrence, returns 16
= Text.PositionOf( "We all want pasta", "a", Occurrence.Last ) 
= Text.PositionOf( "We all want pasta", "a", 1 ) 

//  Occurrence.All returns all occurrences, returns { 3, 8, 13, 16 }
= Text.PositionOf( "We all want pasta", "a", Occurrence.All ) 
= Text.PositionOf( "We all want pasta", "a", 2) 

// You can also return the 2nd position from the end of the string
= List.Reverse( 
     Text.PositionOf( "We all want pasta", "a", Occurrence.All ) ) {1}

// By default the function searches capital sensitive
= Text.PositionOf( "We all Want pasta", "w", 1 ) // Returns -1

//  Add an optional comparer to control the comparison.
= Text.PositionOf( "We all Want pasta", "w", 1, 
       Comparer.OrdinalIgnoreCase ) // Returns 7 

Then there is the Text.PositionOfAny function that returns the position of the first occurrence of the characters provided in a list. The function only takes a list of single-character strings. Optionally you can provide an occurrence parameter to indicate which occurrence position to return.

= Text.PositionOfAny( "You like guacemole", { "e" } )      // Returns 7
= Text.PositionOfAny( "You like guacemole", { "e", "u" } ) // Returns 2

// Occurrence parameters determine the position(s) to return
= Text.PositionOfAny( "You like guacemole", { "e", "u" },
     Occurrence.First ) // Returns 2
= Text.PositionOfAny( "You like guacemole", { "e", "u" }, 
     Occurrence.Last )  // Returns 17
= Text.PositionOfAny( "You like guacemole", { "e", "u" }, 
     Occurrence.All )   // Returns { 2, 7, 10, 13, 17 }

6. Encode to different Value

You can use differenttext functions to transform values from one data type to another.

= Text.From ( 123 ) // Returns "123"

// Tests if Power Query recognizes the string as a number
= Text.InferNumberType( "€11.11" ) // Returns number

// Returns DataFormat.Error: 
// We couldn't match 'ABC' with any known number pattern.
= Text.InferNumberType( "ABC" ) 

In M different functions use Unicode character values. To find out which character represent these you can use the functions Character.FromNumber and Character.ToNumber.

= Character.ToNumber( "!" )  // Returns 33
= Character.FromNumber( 33 ) // Returns "!"
= Character.FromNumber( 35 ) // Returns "#"

// You can use these characters to return a list of values. 
// You can return the unicode characters representing 33-35:
= { "!" .. "#" } // Returns = { "!", """", "#" }

7. Other Text Functions

Lastly, there are some function that may be useful, yet I have not worked with them. This article won’t go into more detail. This is regarding:

Text.NewGuid
JSON.FromValue
Guid.From
Text.FromBinary
Text.ToBinary

That was a long post with lots of examples. Hopefully you learned something new. Are there text functions you want to see more content on? Let me know in the comments.

If you like learning from examples, also make sure to check out the posts:

Enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

13 thoughts on “Text Functions in Power Query M (150+ Examples)”

    • Charlot – thank you very much for pointing this out. It seems the behavior of the function is slightly different than I initially thought. I adjusted the article.

      The function can return three types at the occurrence parameter.

      Occurrence.First (0)
      Occurrence.Last (1)
      Occurrence.All (2)

      You can replace the long-typed words by the index numbers 0, 1 and 2. With this in mind below sets of statements are identical:

      // The first occurrence of a value is returned by default, returns 3
      = Text.PositionOf( "We all want pasta", "a", Occurrence.First ) 
      = Text.PositionOf( "We all want pasta", "a", 0 ) 
      
      // Occurrence.Last returns the last occurrence, returns 16
      = Text.PositionOf( "We all want pasta", "a", Occurrence.Last ) 
      = Text.PositionOf( "We all want pasta", "a", 1 ) 
      
      //  Occurrence.All returns all occurrences, returns { 3, 8, 13, 16 }
      = Text.PositionOf( "We all want pasta", "a", Occurrence.All ) 
      = Text.PositionOf( "We all want pasta", "a", 2) 
      

      Thanks for pointing this out 😁

      Reply
  1. Excellent explanation Thanks.

    How do I go about remove the hyphen on left or right of a letter in a string, but do nothing with the hyphen if it’s between numbers. Example below:
    00-CM-00-12
    00C-M-00-12
    00CM-00-12
    After transform steps, or in one step returns
    00CM00-12

    Thanks 🙏

    Reply
    • Bei,

      That’s a tough requirement. Especially since the characters between “-” don’t always just show numbers or letters, but sometimes combo’s too: Give this a try (paste the code in the advanced editor):

      let
          Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDQdfbVBZKGRkqxOtFKEA6CD+EARRF8ICfIBVU9Ct/QCCIEVe+s64tiHsK6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
          #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList( [Test] ), type list ),
          #"Added Custom1" = Table.AddColumn(#"Added Custom", "NextItem", each List.RemoveFirstN( [Custom], 1 ) & {null}),
          #"Added Custom2" = Table.AddColumn(#"Added Custom1", "PreviousItem", each {null} & List.RemoveLastN( [Custom], 1 )),
          #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each Table.FromColumns( { [PreviousItem], [Custom], [NextItem] } , type table[PreviousCharacter, Current, NextCharacter])),
          #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.2", each Table.AddColumn( [Custom.1], "NewValue",
      each if ( Value.Is( Value.FromText( [PreviousCharacter] ), type text ) or Value.Is( Value.FromText( [NextCharacter] ), type text ) ) and [Current] = "-" then "" else [Current] )),
          #"Added Custom5" = Table.AddColumn(#"Added Custom4", "NewText", each Text.Combine( [Custom.2][NewValue]) , type text ),
          #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Test", "NewText"})
      in
          #"Removed Other Columns"
      
      Reply
    • Hi Fahed,

      If you want to check if a value is included in a cell you can use:

      = Text.Contains( [Text], "ValueToLookFor" )

      if you then want to make sure it is not in there, you can use:

       = not Text.Contains( [Text], "ValueToLookFor" )

      Hope that helps!

      Reply
  2. Hi Rick, this is very clearly written and a fantastic resource. Thanks for sharing it and keep up the great work!

    Cheers,
    Ken

    Reply
    • This instance should return true, thanks Konstantin! I adjusted it right away. With the number of examples and changing things around some mistakes slip in! Your comments are highly appreciated.

      Reply
    • Quyet, I’m so happy to hear you are enjoying these posts. Make sure to come back occasionally, because I’m planning more posts like this.

      😎 cheers, Rick

      Reply

Leave a comment