Decimal data strange issue

[Zorgonaute84]

Hello,

I'm using PowerBI from 2 months ago now, and I occure a very strange issue regarding decimal data.

In fact, my data is a duration in hours. So 20 minutes is 0.3 hours. The problem with my data is some lines are correct, but someones are multiplied by 100 without any reasons.

I'm connected to my data with a OLEDB connector and when I configured the same connection into Excel, the data are correct. Any idea ?

Thanks a lot for your help.

[Zorgonaute84 · Accepted solution]

Hello,

I finally fixed my issue !

I CAST my field as a float in my SQL request :

SELECT CAST(GA_NBH as Float) as GANBH

So my new field GANBH is now correct !!!  No idea why I had to do that, and why some value was correct and other not.

[v-xjiin-msft]

@Zorgonaute84

Based on your description, it seems like your issue is related to something like decimal rounding which is caused by ODBC connector. There may exists compatibility issue between the connector and Power BI.

To troubleshooting it, I would suggest you to keep one time type field data in source table and after importing the source table in Power BI, do the formatting at Power BI desktop side. To verify if the issue persists.

Thanks,
Xi Jin.

[Zorgonaute84]

Hello,

Thanks for your help.

But I have the issue in the Query Editor and in Power BI. I tried to localize the decimal value, to apply rounding etc... But nothing change. The strange thing is, I used the same OLEDB connector into Excel, and I havn't this issue. The data are correct.

Any idea ?

Thanks

[v-xjiin-msft]

@Zorgonaute84

Power BI is different with Excel for OLEDB connector.

Could you please share us some screenshots about your issue if possible? So that we can understand your issue more clearly.

Thanks,
Xi Jin.

[Zorgonaute84]

This is a screenshot from PowerBI :

The same SQL request from Excel :

And the values display in the original source :

The value from Excel are good, but from PowerBI the 1st is correct, but the second is multiplied by 100.

[v-xjiin-msft]

@Zorgonaute84

So, in your original souce the values are time. However in Power BI or Excel, they are decimals. How did you format time to decimal? Could you please share us your expression or something which can help us to make a test if possible?

Thanks,
Xi Jin.

[Zorgonaute84]

Hello,

No, I didn't reformat anything. The raw format is decimal (it's represent the duration in hours). And it's in the original source where the value is reformated like hh:mm:ss

So for 00:25:00 in the original source (25 minutes), I should have 0.416667 in GBH (0.41 hours).

[Zorgonaute84]

up

[v-xjiin-msft]

@Zorgonaute84

Sorry for delayed reply. 

I have made a simple test, however I didn't encounter your issue. My Power BI desktop version is (Feb 2018 update), I'm using OLE DB Provider for SQL Server connecter. And my source data is from a common SQL Server table (decimaltest) and the val column is Time type:

I have used following T-SQL script to convert Time to Decimal:

select *,cast(DATEDIFF(mi,CONVERT(time,'00:00:00'), CONVERT(time,val))/60.0 as decimal(6,5)) as duration
from decimaltest

Then I put above script in Advanced options when getting data from OLE DB. The result is Power BI desktop is like this:

 

It works fine. 

Thereby, please kindly make a more detailed description about how you get data from OLE DB in Power BI? And what's the difference between my sample?

Thanks,
Xi Jin.

[Zorgonaute84]

Hello,

I'm using PowerBI from 2 months ago now, and I occure a very strange issue regarding decimal data.

In fact, my data is a duration in hours. So 20 minutes is 0.3 hours. The problem with my data is some lines are correct, but someones are multiplied by 100 without any reasons.

I'm connected to my data with a OLEDB connector and when I configured the same connection into Excel, the data are correct. Any idea ?

Thanks a lot for your help.