Expression.Error: The name 'Text.BeforeDelimiter' wasn't recognized. Excel 2016

0
votes

I have an excel "Add Column" from Power Query editor. My data looks like this,

26567-5698
51254-5458
6954-1545
45415
56541
5621
..

Some have 4 digits before - and some have 5 digits before -. For those values that have - in between, I like to extract the first part (before delimiter).

I tried the following, 

if  
    Text.Length(Text.BeforeDelimiter([MyCol], "-")) = 4   
  then
    "0" & Text.Start([MyCol],4)
 else if  
     Text.Length(Text.BeforeDelimiter([MyCol], "-")) = 5   
  then 
     Text.Start([MyCol],5)
else
   [MyCol]

If the length before delimiter I am adding a 0 and first 4 digits. Otherwise, I want the first 5 digits.

When I do the above, I get the following error:

Expression.Error: The name 'Text.BeforeDelimiter' wasn't recognized. Make sure it's spelled correctly.

Here is the documentation I am following.

I am using Excel 2016. I have been searching and could not find anything related to this. Any help would be appreciated.

1
excelpowerqueryexcel-2016
Possible duplicate of Having trouble with Text.BeforeDelimiter – Alexis Olson
@AlexisOlson Any alternative ways to reach my goal in power query? – user9431057
There are other ways, but are you unable to update your Excel? This should be rolled into Excel 2016 through normal updates by now. – Wedge
@Wedge "This should be rolled into Excel 2016 through normal updates by now" I am using Excel 2016 and still an issue for now! – user9431057
@Wedge would you be able to give a hint on other ways please? I was thinking of splitting the text by delimiter, but having a hard time taking the first part after splitting – user9431057

1 Answers

1
votes

You can do a workaround with other text functions.

Left  = try Text.Start([MyCol], Text.PositionOf([MyCol], "-")) otherwise [MyCol]
Right = try Text.Range([MyCol], Text.Length([Left]) + 1) otherwise null