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.

     

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.