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-xiaoyan-msft
on:
Report jumps between two profile logins.
on:
Get External Data: Power BI semantic models throws...
on:
Power BI Service - NetworkConnectionIssue when sav...
Idrissshatila
on:
Timeline slicer is showing 30 & 31 March under 30 ...
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