Text.Middle not working Excel 2016

[volius]

Hi,

After Excel update from 2013 to 2016 version not working my oldest files with Power Query commands Text.Middle. i get the error "Text.Middle wasn't recognized"

if (Text.Length(

) = 25) then Text.Middle([Code],0,17) else Text.Middle([Code],0,10)

How fix this problem?

 

[sandy666]

I don't see any problem with your IF. It works

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each if (Text.Length([PLAIN][Code][/PLAIN]) = 25) then Text.Middle([PLAIN][Code][/PLAIN],0,17) else Text.Middle([PLAIN][Code][/PLAIN],0,10)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Code"})
in
    #"Removed Columns"[/SIZE]

Code		Result
abcdefghijklmnopr12345678		abcdefghijklmnopr
abcdefghij12345		abcdefghij

 

[VBA Geek]

Strange I dont have this issue. Are you sure the error does not come from somewhere else in the code? Also try to download the latest version of Power Query or Excel 2016 if you are on Office 365

anyway you can always use Text.Range as an alternative

 

[volius]

Thank, i fix problem with Text.Range