Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
3 years ago
Solved

Convert Text Time Duration

I reviewed various previous threads on this matter, but they all seemed to confuse me even further.     When I import my Excel report it stores the data below as text, no matter what I format them ...
Need Help
  • CoreyP's avatar
    CoreyP
    3 years ago

    Try reading through this chapter on all the duration functions: https://learn.microsoft.com/en-us/powerquery-m/duration-functions.

     

    I think you'll want to convert to duration from text first. Then parse out your hours, minutes, seconds and either aggregate them separately or convert them all into the same unit and then aggregate. 

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
3 years ago

Have you tried using the Duration functions in Power Query? 

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
3 years ago

I'm a rookie so bare with me 🙂  But I found the Duration Function within Power Query and it changed the data to a decimal number.  You can see the comparison here between a converted column and a non-converted column.  But what is this decimal telling me? A fraction/decimal of how many days/hours?  If I know the basis of the decimal I think I can then convert to hours/minutes/seconds?  

 

 

 

Thank you so much already!! 

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Try reading through this chapter on all the duration functions: https://learn.microsoft.com/en-us/powerquery-m/duration-functions.

     

    I think you'll want to convert to duration from text first. Then parse out your hours, minutes, seconds and either aggregate them separately or convert them all into the same unit and then aggregate. 

    • mclawler's avatar
      mclawler
      Icon for Helper III rankHelper III
      3 years ago

      That's perfect!!  Thank you, I think you've got me on the right track now.  Much appreciated!