Converting Minutes to a HH:MM Format: A Power Query Solution
Last week, I was presented with a seemingly easy problem. I was working with a dataset including a column with minutes. Ranging anywhere from 0 to 50000 minutes. The end goal was to display these minutes in an HH:MM notation, such as 1:05 for 65 minutes.
NEW: 1:05
However, as I attempted to achieve this using Power Query, I encountered unexpected challenges. For instance, when I attempted to save the column with a duration data type, it resulted in 480 minutes being displayed as 480 hours in a DD:HH:MM:SS format.
After trying a few different methods, I created my own one. It's based on simple reasoning.
When you take any amount of minutes and divide it by 60, the amount before the comma will be your hours. The part after the comma can be multiplied by 60. When combining the two, you'll have an HH:MM notation based off seconds. and then multiplied by 100 to turn into seconds. Here are some examples:
Now we tested this calculation, it's time to wrap it in a Power Query Function. I've made the following one:
= Number.IntegerDivide([#"Minutes"],60) + Number.Mod([#"Minutes"],60)/100
This function can be used to create a new column like this:
Keep in mind to use your own column names. In my example, the column containing minutes is "Arrival Delay in Minutes".
After saving the column, you should see the desired HH:MM in a new column:
Do you rather watch than read? You can find the YouTube video relating to this article here:
I hope this was helpful to you. If you'd like to see or read more, subscribe to my YouTube Channel or to my newsletter in which I share new posts from this website. You can also email me on: danielroos01@protonmail.com to ask any questions.
Furthermore if you'd like to see the documentation I've used to create this function, follow this link to WikiHow:
Grace Imson, MA