cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI Desktop returns wrong values via ODBC DB2 connection

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_ok.gifDB2 query shows correct value

PBI_wrong.gifPower 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. 

PBI_String.gif

 

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

Status: New
Comments
Moderator

Hi @Daniel_Altevogt

 

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

 

Best Regards,
Qiuyun Yu 

Frequent Visitor

Hello@v-qiuyu-msft 

 

I've send you a seperate message with DDL.

 

Many thanks

Daniel

Frequent Visitor

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

 

 

Frequent Visitor

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