Convert hour format data to decimal

[Pmorg73]

Hi

I have seen advice on going the other way. But I have a large data set that exports the data in hours (Example below). I intend to sum this time so need to convert to decimal. In Excel this is simply done by *24. Any suggestions please?

I have up to now fixed this in excel then imported that file. But it would be nice to just have csv export and refresh for future versions.

[CheenuSing]

Hi @Pmorg73 

Can you please load sampledata , output expected  to Google / One Drive and share the link here.

Cheers

CheenuSing 

[Pmorg73]

Excel sheet here with basic data. I thinned it down to two elements, with mutliple entries. I have made it anonimous so it should be good to go. My actaul data is several thousand entries from 2013 to now.

https://batchelarmcdougall-my.sharepoint.com/:x:/g/personal/phil_bmconsult_co_nz/Ee_FqH7f1zlDmU1FlN8...

Our database spits out the time column as hours. Then in Power BI it makes it a long date format (orignal image). In excel I would add a column and simply =Time *24 and get the decimal version. This is what I need to do in Power BI

I have seen people discussing going the other way around on here (ie decimal to time format)

[CheenuSing]

Hi @Pmorg73 

Have you tried createing a calculated column

DecimalHours = Table[Time] * 24

replace Table by your tablename

Cheers

CheenuSing

[Pmorg73]

Yeah i tried that before I posted the query on here, but it does not work correctly. The formatting of the data is the issue I think. That converts it in to completely wrong answers. (see image)

I have uploaded a BI file with just the data inserted. I did add a column in the excel sheet before I brought it in of the Excel *24 answers that we would need to achieve. I then added a column and did the calc as suggested. See the image the numbers are not correct. I assume it is related to the format of the time entry when I bring it from excel to Power BI, as it is showing as a long date format, but in excel it is an hours format. (blue arrow in image)

link to BI file

https://batchelarmcdougall-my.sharepoint.com/:u:/g/personal/phil_bmconsult_co_nz/EZPLVTavypxPhdui_Cj...

Link to excel data

https://batchelarmcdougall-my.sharepoint.com/:x:/g/personal/phil_bmconsult_co_nz/Ee_FqH7f1zlDmU1FlN8...

Any suggestions greatly appreciated

[CheenuSing · Accepted solution]

Hi @Pmorg73 

I see that the format for time taken as a long date and hence the formula gives different value.

Going through your file, assuming the time is shown in longdate format then I tweeked the formula

Time*24 = 'Table1'[[Time]] Time]*24 - 24

By doing so I got the result the same as Time*24 done in excel and ported

over.

Cheers

CheenuSing

[Pmorg73]

Does seem to work. Thanks for that. So I understand it what does the -24 do? 

Time*24 = 'Table1'[[Time]] Time]*24 - 24

[CheenuSing]

Hi @Pmorg73 ,

I was checking the values without '-24' . Everything was more by 24.  So I decided to substract 24 and check. It worked.

Cheers

CheenuSing