cancel
Showing results for 
Search instead for 
Did you mean: 

Power Query Text.PositionOf incorrectly translated into Native Query while connected to an MS SQL DB

Hi,

As per official documentation Text.PositionOf - PowerQuery M | Microsoft Docs, 

Text.PositionOf(text as text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as any

This function returns the position of the specified occurrence of a substring found in text.  Adding this as a new column via

 

Text.PositionOf([text], ".")

 

to find a position of a dot in the column "text", in turn, converts this function into the following expression in Native Query: 

 

charindex([_].[text], '.') - 1 as [Custom]

 

However, as per official SQL documentation CHARINDEX (Transact-SQL) - SQL Server | Microsoft Docs,

 

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )   

 

the substring should come first, so the correct expression in Native Query would be:

 

charindex('.', [_].[text]) - 1 as [Custom]

 

As a result, the current implementation always returns -1 suggesting that the substring cannot be found. I've also found that switching substring and text in the Power Query function returns the desired result, but in such a case the position of substring and text is not correct as per official documentation. 

Could you please check and correct if necessary?

Regards,

Karim 

Status: Investigating

Hi @kabdrx 

 

May I know how you create your column? I’ve tested according to the description, but it works well and there will be no Native Query for custom column.

vcazhengmsft_0-1643362627720.png

 

Best Regards,

Community Support Team _ Caiyun

Comments
v-cazheng-msft
Community Support
Status changed to: Investigating

Hi @kabdrx 

 

May I know how you create your column? I’ve tested according to the description, but it works well and there will be no Native Query for custom column.

vcazhengmsft_0-1643362627720.png

 

Best Regards,

Community Support Team _ Caiyun

kabdrx
Regular Visitor

Hi @v-cazheng-msft ,

Are you connected to an MS SQL instance? I'm in import mode, you can see the steps below:

kabdrx_0-1643373369748.png

kabdrx_1-1643373419888.png

And the result of the function:

kabdrx_0-1643373806981.png

 

I'm using the latest version of PBI Desktop. 

Regards,

Karim

kabdrx
Regular Visitor

Figured I should post the "fix" too:

kabdrx_4-1643373743656.png

 

kabdrx_2-1643373686401.png

And the result:

kabdrx_3-1643373724699.png

 

Regards,

Karim

 

v-cazheng-msft
Community Support

Hi @kabdrx 

 

Thanks for your reply! I basically understand this issue. May I know whether you are using the latest version of Power BI Desktop? You can check your desktop version by Help>About>Version. If you create a new report to test on this M function, will you get same issue?

vcazhengmsft_0-1643708801136.png

 

Best Regards,

Community Support Team _ Caiyun

kabdrx
Regular Visitor

Hi @v-cazheng-msft,

Sorry for the delay. This is the version that I have:

kabdrx_1-1643985774890.png

And yes, I've tested with a new report, and it has the same "translation" into Native Query. 

Regards,

Karim

kabdrx
Regular Visitor

Hello @v-cazheng-msft,

Do you have any updates on this?

Thanks

Karim