Expression.Error: The name 'Text.BeforeDelimit

问题:

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:

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