Time Formats issue while importing from Excel to PowerBI

[mrk777]

Hi Team, I have brought this issue to the community before, but later I realized that the answer that I have got is not actually needed and the problem is actually bigger than I thought.

Here's my problem in detail: I have "Total Time Consumed" & "Actual Availability" in Excel populating the total sum of hours for the specific month in each row/record. (Attached is the screenshot for your reference):

However, when I'm trying to pull the same data to PowerBI, it is not reading the data in that way and the formats are completely different (attached is the screenshot for your reference):

I tried many ways in adding the new column and formulas to change the format and numbers as per the Excel file, but I couldn't achieve it. Following are a few methods I tried (I ended up getting incorrect values (not errors)):

ConsumedHours1 = DATEDIFF("1900-01-01",DATEVALUE('Utilization & Bandwidth'[Total Time Consumed]),HOUR)+(MINUTE(TIMEVALUE('Utilization & Bandwidth'[Total Time Consumed]))/60)+(SECOND(TIMEVALUE('Utilization & Bandwidth'[Total Time Consumed]))/3600)

DATEDIFF("01/01/1900",DATEVALUE('Utilization & Bandwidth'[Total Time Consumed]),HOUR)+DATEDIFF("01/01/1900",DATEVALUE('Utilization & Bandwidth'[Total Time Consumed]),MINUTE)+(DATEDIFF("01/01/1900",DATEVALUE('Utilization & Bandwidth'[Total Time Consumed]),SECOND))

Request: I would like to get at least the total number of hours mentioned in the Excel file into PowerBI.

Please help me in solving this case. Thank you for your help in advance.

[mrk777]

Thank you for your quick response and your solution is working for me.

Alternatively, I have tried the other way around after trying many possibilities, I was able to achieve the same, but in the form of Whole Numbers and decimals. The procedure is as follows:

Column = ('Utilization & Bandwidth'[Total Time Consumed] * 24) - 24

Please suggest me if I'm going wrong. Appreciate your help, thank you!

[v-yueyunzh-msft · Accepted solution]

Hi , @mrk777 

According to your description, when you load the data to Power BI Desktop , it get the error date type and you want to get the hours of this date .

For your need , i test it in my side , here are the steps you can refer to :
(1)This  is my test data:

(2)We can add a custom column in Power Query Editor:

We can add two columns using this M code :

Duration.Days(Duration.From([#"Total Time Consumed(in Hours)"] - #datetime(1899,12,31,0,0,0) ) ) *24 + Duration.Hours(Duration.From([#"Total Time Consumed(in Hours)"] - #datetime(1899,12,31,0,0,0) ))

Duration.Days(Duration.From([#"Actual  Avaliablity(in Hours)"] - #datetime(1899,12,31,0,0,0) ) ) *24 + Duration.Hours(Duration.From([#"Actual  Avaliablity(in Hours)"] - #datetime(1899,12,31,0,0,0) ))

Then we can meet your need, the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly