Problem with accented characters while creating custom column

[MarkoStam]

Hey,

I direct query data from an oracle database. I have problem with accented characters when creating a custom column.

In power query I am trying to create a custom column with the folowing formula:

if [column1] = "INV" then "XER Neznáme HU"
else if [column1] = "N/A" then "XNA Chybějící HU"
else [column1] & " - " & [column2]

But I get an error:

DataSource.Error: Oracle: ORA-12704: character set mismatch
Details:

DataSourceKind=Oracle
DataSourcePath=orfeus.osa.cz:1521/biosa.osa.cz
Message=ORA-12704: character set mismatch
ErrorCode=-2147467259

Column 1 data examples (data type is Text): 06; 0059; NA; INV

Column 2 data examples (data type is Text): vícenosiče; smluvní pokuta - VP

When I don't use accented character in the formula it does work:

if [column1] = "INV" then "XER Nezname HU"
else if [column1] = "N/A" then "XNA Chybejici HU"
else [column1] & " - " & [column2]

I do have the Regional Settings - Model language set to Czech.
And I do have AL32UTF8 Unicode character set used in the Oracle database.

What should I do so I can return the text with the accented characters?

[OwenAuger · Accepted solution]

Hi @MarkoStam 

I was able to reproduce your error with a test Oracle database.

This is pretty frustrating!

Conditions to produce the error

From my testing, the conditions required to produce this error are:

• Adding a conditional column to an Oracle-sourced table in Power Query where:
  • At least one conditional branch produces text containing a "special" character (such as "á"); and
  • At least one conditional branch produces text not guaranteed to contain a "special" character.

I'm not sure exacly what qualifies as a special character however 😉

In your example, the first two branches produce results contain special characters, but the third branch concatenating column1 and column2 is not guaranteed to.

Possible solutions/workarounds:

1. Create a view in the database adding the conditional column (preferred).

For example:

SELECT 
    column1, 
    column2, 
    CASE 
        WHEN column1 = 'INV' THEN 'XER Neznáme HU'
        WHEN column1 = 'N/A' THEN 'XNA Chybějící HU'
        ELSE column1 || ' - ' || column2 
    END AS CustomColumn
FROM SourceTable

2. Pass the query as a native query in Power Query (not preferred).

For example:

let
    Source = Oracle.Database("YourDatabase",[Query = "select column1, column2, ... from ..."])
in
    Source

or

let
    Source = Oracle.Database("ozerdb_high"),
    Query = Value.NativeQuery(Source, "select column1, column2, ... from ...")
in
    Query

3. Alternatively, you could ensure that the text returned on each conditional branch includes a "special" character (invisible if necessary).

In my testing, this worked. The first two branches already have special characters, and I added a zero-width space in the 3rd branch.

if [column1] = "INV" then "XER Neznáme HU"
else if [column1] = "N/A" then "XNA Chybějící HU"
else [column1] & " - " & [column2] & "#(200B)"

This is not ideal but it may work if an invisible character won't break any logic downstream.

Feedback to Microsoft

I suggest placing some feedback and I will do the same when I have a chance.

The issue appears to lie somewhere in "query folding" translation.

Regards

[MarkoStam]

Hi @OwenAuger,

thank you for your time and effort.

The help is much appreciated as I spent quite some time trying to resolve the problem myself. 

I have used the third solution you have written. 

I will be writing a feedback to Microsoft.

Thank you once more.