Forum Discussion

Zorgonaute84's avatar
Zorgonaute84
Regular Visitor
8 years ago
Solved

Decimal data strange issue

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...
  • Zorgonaute84's avatar
    Zorgonaute84
    8 years ago

    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's avatar
v-xjiin-msft
Solution Sage
8 years ago

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's avatar
Zorgonaute84
Regular Visitor
8 years ago

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's avatar
    Zorgonaute84
    Regular Visitor
    8 years ago

    up

    • v-xjiin-msft's avatar
      v-xjiin-msft
      Solution Sage
      8 years ago

      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.