Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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:
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
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:
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
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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |