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
v-yetao1-msft
on:
Livestreaming Dashboard Timestamp Issue
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.
Best Regards,
Community Support Team _ Caiyun