Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
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!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
164 | |
86 | |
68 | |
68 | |
58 |