SOLVED
Home

M Language Syntax problem from Excel 2013 to 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-539489%22%20slang%3D%22en-US%22%3EM%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539489%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3CBR%20%2F%3Ei%20cant%20figure%20out%20why%20i%20am%20getting%20this%20problem%20after%20passing%20from%20excel%202013%20to%202016%20(upgrading%20to%20Office%20365).%3CBR%20%2F%3EThe%20syntax%20seems%20correct%20and%20also%20Power%20Query%20editor%20says%20that%20it%20is%20fine%20but%20then%20the%20step%20is%20considered%20wrong.%3CBR%20%2F%3E%3DText.Select(Text.Start(%5BIdRisorsa%5D%2C2)%2C%7B%22A%22..%22Z%22%7D)%3C%2FP%3E%3CP%3EThe%20formula%20worked%20perfectly%20in%20excel%202013%20then%2C%20with%20the%20passage%20to%20the%20new%20version%2C%20it%20started%20giving%20error.%20The%20error%20is%20%22Expression.Error%3A%20The%20name%20'Text.Select'%20wasn't%20recognized.%20Make%20sure%20it's%20spelled%20correctly.%22%3CBR%20%2F%3EMoreover%20the%20exact%20same%20formula%20works%20on%20another%20PC%20and%20other%20Power%20query%20users%20confirmed%20the%20formula%20works%20fine%20on%20their%20computers.%20I%20cant%20understand%20what%20is%20going%20on.%20If%20i%20insert%20the%20formula%20in%20formula%20bar%20everything%20is%20ok.%3CBR%20%2F%3Epls%20help%20really%20important.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-539489%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539585%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336527%22%20target%3D%22_blank%22%3E%40LucaA1%3C%2FA%3E%26nbsp%3B%2C%20I%20may%20only%20repeat%20the%20screenshort%20from%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FM-Language-Syntax-problem-from-Excel-2013-to-2016%2Ftd-p%2F684472%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FM-Language-Syntax-problem-from-Excel-2013-to-2016%2Ftd-p%2F684472%3C%2FA%3E%26nbsp%3Bbut%20made%20in%20Excel%20Power%20Query%20-%20it%20works.%20Moreover%2C%20if%20you%20type%20it%20in%20formula%20bar%20and%20have%20no%20such%20error%20that's%20not%20the%20syntax%20of%20the%20function%20itself.%20Most%20probably%20that's%20what%20around%20it.%20If%20you%20could%20provide%20small%20sample%20file%20it'll%20be%20easier%20to%20say%20what%20that%20could%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540124%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20of%20that%20model%20are%20sensitive%2C%20but%20i%20am%20gonna%20share%20this%20other%20book%20with%20a%20sample%2C%20and%20text.select%20still%20doesn't%20work.%3C%2FP%3E%3CP%3ECan%20you%20help%20me%3F%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3ELuca%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540170%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540170%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336527%22%20target%3D%22_blank%22%3E%40LucaA1%3C%2FA%3E%26nbsp%3B%2C%20I%20was%20no%20able%20to%20reproduce%2C%20just%20refreshed%20and%20it%20works%20(attached).%20If%20you%20check%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20412px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112430i45DD74F367C4B348%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhat%20is%20your%20Power%20Query%20version%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540204%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.49.4831.381%2032-bit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20have%20a%2032-bit%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540295%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336527%22%20target%3D%22_blank%22%3E%40LucaA1%3C%2FA%3E%26nbsp%3B%2C%20I%20didn't%20find%20the%20version%20history%2C%20but%202.49%20is%20mentioned%20more%20than%20an%20year%20ago.%20Text.Select%20also%20was%20introduced%20about%20an%20year%20ago.%20Perhaps%20your%20version%20is%20bit%20old.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20check%20if%20function%20is%20supported%20or%20not%20in%20your%20version.%20Start%20blank%20query%2C%20in%20formula%20bar%20type%3C%2FP%3E%0A%3CPRE%3E%3D%20%23shared%3C%2FPRE%3E%0A%3CP%3Eand%20you%20will%20see%20list%20of%20all%20supported%20for%20your%20version%20functions.%20Better%20to%20transform%20that%20list%20into%20the%20table%20and%20sort%20ascending%20-%20it'll%20be%20easier%20to%20check.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540431%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ety.%20You%20are%20right%2C%20it%20looks%20like%20not%20supported%20but%20i%20dont%20undestand%20why.%20I%20moved%20from%20Excel%202013%20to%20Excel%202016%20but%20not%20updated%3F%26nbsp%3B%3C%2FP%3E%3CP%3Ebtw%20i%20think%20that%20was%20the%20problem%3C%2FP%3E%3CP%3Ety%20again%3C%2FP%3E%3CP%3Ebye%3C%2FP%3E%3CP%3ELuca%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540835%22%20slang%3D%22en-US%22%3ERe%3A%20M%20Language%20Syntax%20problem%20from%20Excel%202013%20to%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336527%22%20target%3D%22_blank%22%3E%40LucaA1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELuca%2C%20with%20O365%20it%20shall%20be%20fresh%20version%20after%20the%20update.%20If%20you%20share%20your%20version%20from%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20425px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112449i7E798C9DDD933F70%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit'll%20be%20more%20clear%20how%20old%20is%20it.%20In%20opposite%20to%20PQ%20the%20history%20of%20office%20versions%20is%20available.%3C%2FP%3E%3C%2FLINGO-BODY%3E
LucaA1
New Contributor

Hi All,
i cant figure out why i am getting this problem after passing from excel 2013 to 2016 (upgrading to Office 365).
The syntax seems correct and also Power Query editor says that it is fine but then the step is considered wrong.
=Text.Select(Text.Start([IdRisorsa],2),{"A".."Z"})

The formula worked perfectly in excel 2013 then, with the passage to the new version, it started giving error. The error is "Expression.Error: The name 'Text.Select' wasn't recognized. Make sure it's spelled correctly."
Moreover the exact same formula works on another PC and other Power query users confirmed the formula works fine on their computers. I cant understand what is going on. If i insert the formula in formula bar everything is ok.
pls help really important.

7 Replies

@LucaA1 , I may only repeat the screenshort from here https://community.powerbi.com/t5/Desktop/M-Language-Syntax-problem-from-Excel-2013-to-2016/td-p/6844... but made in Excel Power Query - it works. Moreover, if you type it in formula bar and have no such error that's not the syntax of the function itself. Most probably that's what around it. If you could provide small sample file it'll be easier to say what that could be.

@Sergei Baklan 

Data of that model are sensitive, but i am gonna share this other book with a sample, and text.select still doesn't work.

Can you help me?

thanks

Luca

@LucaA1 , I was no able to reproduce, just refreshed and it works (attached). If you check here

image.png

what is your Power Query version?

@Sergei Baklan 

 

2.49.4831.381 32-bit

 

I also have a 32-bit Excel

Solution

@LucaA1 , I didn't find the version history, but 2.49 is mentioned more than an year ago. Text.Select also was introduced about an year ago. Perhaps your version is bit old.

 

You may check if function is supported or not in your version. Start blank query, in formula bar type

= #shared

and you will see list of all supported for your version functions. Better to transform that list into the table and sort ascending - it'll be easier to check.

 

@Sergei Baklan 

 

ty. You are right, it looks like not supported but i dont undestand why. I moved from Excel 2013 to Excel 2016 but not updated? 

btw i think that was the problem

ty again

bye

Luca

@LucaA1 

 

Luca, with O365 it shall be fresh version after the update. If you share your version from here

image.png

it'll be more clear how old is it. In opposite to PQ the history of office versions is available.