Hi,
I had an issue with one of my PQ processes where I was trying to convert text into a real date. It was working fine up until this month where it failed on my October dates. Upon investigation it seems that the Text.Middle function works differently from the standard Excel MID function.
For example, to extract the month from "02.10.2018" in cell A1 I would use: =MID(A1 , 4 , 2) and this would return "10".
The Text.Middle function equivalent in PQ however is: =Text.Middle(ColumnWithText , 3 , 2)
I was just wondering if anyone else had come across this and/or knew why Microsoft have used different logic?
Also, is there any documentation anywhere that summarises these types of differences to be aware of?
Thanks
Graham
Hi Graham,
I'm not sure why the Excel functions differ from their Power Query equivalents, but it's probably because they wrote the Power Query formula language from scratch, so it wasn't intended to replicate Excel functions. It sure would be easier if they worked in the same way, though.
I'm not aware of any Excel vs Power Query function comparisons, sorry.
Mynda
Hey Graham,
It looks like the M Language Text functions are zero bases, where the first character is in spot zero instead of one. Read the Extraction group on the M Language site at:
https://msdn.microsoft.com/en-us/query-bi/m/text-functions
It looks like all these are Zero-Based. That could be your difference.
Marv
