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
mattlee
on:
Small multiples not fitting to scale
on:
Connection Timeout to Azure Analysis Services (AAS...
Hi @Anonymous
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.
Best Regards,
Community Support Team _ Caiyun