I am working with Power Query in Excel and came across two functions, Text.Middle and Text.Range, which seem to serve the same purpose: extracting a substring from a text value.
I get the same results when I use either function.
Text.Middle("Hello, World", 7) // World
Text.Range("Hello, World", 7) // World
Text.Middle("Hello, World", 0, 5) // Hello
Text.Range("Hello, World", 0, 5) // Hello
The syntax in Microsoft's documentation shows the second parameter for Text.Range is named offset instead of start, but the behavior seems identical.
Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable text
Text.Range(text as nullable text, offset as number, optional count as nullable number) as nullable text
What is the difference between Text.Middle and Text.Range? Is there any reason to use one over the other?
Power Query has lots of ways to solve the same problem and there are other functions that seem like synonyms. In this case, Text.Middle is more forgiving. Try:
= Text.Range("Hello, World", 0, 50)
= Text.Middle("Hello, World", 0, 50)
Text.Range will throw an error if you request too many characters where as Text.Middle returns the text