Power BI Desktop returns wrong values via ODBC DB2 connection

[Anonymous]

Dear Power BI Team,

today I've spend time to investigate an issues where Power BI Desktop (RS) 2018-08 returns wrong values when using an ODBC DB2 connection. When I using same ODBC connection using Excel correct values are returned, also using DB2 directly.

When I use Power BI Desktop RS to connect to DB2 via ODBC Power BI Desktop client seems to translate decimal number initiator "," or "." wrong. Excel does correctly. So when I a value e.g. "1234.23" Power BI Desktop would return "123423", Excel would return corect "1234.23" value. I've tested several settings within Power BI Desktop ODBC connection string and/or transformating and setting values to Power BI Desktop query, but every time witin Power BI Desktop (RS) wrong values are returned. I've also testet using different langauge settings within Windows Server regional profile and/or general Power BI Desktop regional setting.

DB2 query shows correct value

Power BI Desktop shoes none correct value

As you can see from screenshot, it looks like Power BI Desktop seems not to respect "," at ",80" therefore returned value is with two digest higher than it should be.

I've tested several options within Power BI Desktop ODBC connection string as well, but none used to be fitting getting rid of this issue. 

Is there any known issue about it, or can you provide some kind of additional information that value is shown correctly?

Please don't hesitate to contact me for additional information.

Many thanks

Daniel

[v-qiuyu-msft]

Hi @Anonymous, 

Would you please share DDL so we can try to create a sample table to test it? 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hello@v-qiuyu-msft 

I've send you a seperate message with DDL.

Many thanks

Daniel

[Anonymous]

Hello @v-qiuyu-msft ,

I'd like to ask if you might have any news on this topic for me?

Do you need any additional information regarding topic?

Looking forward to your answer.

Many thanks

Daniel

[Anonymous]

Dear all,

I just wanted to add solution for everyone who might exprect issue.

You need tro add an value to ODBC connection offered by IBM.

CLI/ODBC PATCH1 and PATCH2 configuration keywords values

You'll find at "PATCH2" an value "15" saying:

Causes a period separator to be used instead of the default locale's decimal separator in character output.

So even if Microsoft ODBC Log reports correct values you'll need to add an entry at ODBC Connection itself or at Power BI Connection to get correct values.

Many thanks

Daniel

[Anonymous]

Dear all,

we've also expected this behaviour using Oracle ODBC and found again connection parameters you can add to connection to get it sorted.

Source: https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1177

Hint:

NUM

Numeric Settings

NLS=Use Oracle NLS numeric settings (to determine the decimal and group separator). MS=Use Microsoft regional settings. US=Use US settings.

Many thanks

Daniel

[Anonymous]

I have this exact same issue with a Teradata ODBC driver, does anyone have a solution for Teradata?

[Anonymous]

Hello @Anonymous ,

Teradata has got some ODBC connection parameters as well. I'm not using Teradata but you might take a look at below link.

Link: Teradata ODBC Driver Options • ODBC Driver for Teradata® User Guide • Reader • Teradata Online Documentation | Quick access to technical manuals

Within "Teradata ODBC Driver Options" area you will find a option for "Use Regional Settings for Decimal Symbol" which you might test at your environment.

Best regards

Daniel