Forum Discussion

mrk777's avatar
mrk777
Frequent Visitor
3 years ago
Solved

Time Formats issue while importing from Excel to PowerBI

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 m...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

mrk777's avatar
mrk777
Frequent Visitor
3 years ago

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!