DataSource.Error when using Text.Trim connected to Azure Synapse

[SamSteere]

Issue

Text.Trim query folds to a statement that can't be executed by Azure Synapse. This is because Power BI uses the same logic as it does when connecting to a Sql Database, but that syntax is not supported by Azure Synapse

Power Query Code

Code generated by query folding

trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from convert(nvarchar(max), [_].[row_id])) as [t0_0],

Error Message

Syntax from Microsoft Documentation

Reproduction Steps

- Connect to an Azure Synapse Serverless Database using the Azure Synapse Analytics SQL connector (or Azure SQL database, they both use Sql.Database)

- Import a table

- Transform a Column using the built in Text.Trim Power Query transformation

Idea

Generate a query that matches the azure synapse syntax when connected to azure synapse

[Anonymous]

Hi @SamSteere 

Based on your description , you can put your ideas to Ideas Forum .  If a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Ideas (powerbi.com)

Best Regards,
Community Support Team _ Ailsa Tao

[HenkStandhardt]

Hi @Anonymous ,

The 'Idea' that @SamSteere brings up in his Issue is a kind way of telling you how to possibly fix the issue. The message is not a feature request. It's a request to fix a bug.
I'm also hit by this bug.

Simply transforming the [name] column in the [account_partitioned] table of a dataverse database (that is presented as a lake database in the Synapse workspace after configuring the Azure Synapse Link for Dataverse) with Trim in PowerQuery UI results in the error messages. There seems to be no easy way to workaround it.
The documentation about Text.Trim (https://learn.microsoft.com/en-us/powerquery-m/text-trim) mentions some parameters that might help, but the explanation and examples are not sufficient to try it.
Please advise about a workaround and/or update the documentation and/or fix the error.

Regards,

Henk.