Problem with accented characters while creating custom column

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