Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

0

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

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

SamSteere_2-1660716029199.png

 

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

SamSteere_0-1660715632159.png


Syntax from Microsoft Documentation

SamSteere_1-1660715709228.png


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

Status: Delivered
Comments
Anonymous
Not applicable

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
Regular Visitor

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.