Wrong encoding when reading from Azure Synapse View on top of Parquet files

[Anonymous]

Hi, 

I have a view in Azure Synapse on top of my parquet files, I've created it like this:

CREATE VIEW [dbo].[v_tmp] AS ( 

    SELECT * FROM 

    OPENROWSET( BULK 'https://***.dfs.core.windows.net/dwh/tmp/*.parquet', FORMAT='PARQUET') 

    AS [result])

When I query this view in Synapse, everything goes as expected. 

But when I read from in it Power BI, there is a wrong encoding towards UTF-8. E.g. "Houzé" becomes "Houzé". 
I already tried:

• To put the database in Synapse in UTF-8 as described here.
• To transform the read in data column to UTF-8 using textEncoding.

Is there someone here with other ideas that I could try to overcome this issue?

Thanks a lot!

[Anonymous · Accepted solution]

Afterall I found the solution: you should indeed change the collation of the database using the query below. But I also needed to recreate the views on top. Probably something was cached. I have the correct encoded data in power BI now. 

ALTER DATABASE datawarehouse 

    COLLATE Latin1_General_100_BIN2_UTF8;

Thanks for all of your input!

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to get data directly from 'https://***.dfs.core.windows.net/dwh/tmp/*.parquet instead of using view from Azure Synapse?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thanks for your answer!

This does indeed work, but it is not what I want because I would like to keep my Power BI clean and user the Serverless pools to connect to. 

[lbendlin]

What happens when you use the query as your data source (rather than the view)?

[Anonymous]

Hi, 

When I query directly on the parquet file from within powerBI the encoding goes correctly. So the error must be somewhere in Synapse then.

Any idea what I can test more?

Thanks a lot for your input!