Forum Discussion
Time format export data from hours to decimal
I have an interesting query
I am working in time and invoice management web site called workflow max. To get data from the web site I export reports in csv format.
For small time entries the csv exports in a hh:mm format. But for the big ones in the image it is [h]:mm:ss. (image is a filtered view only)
This is happening when the sum of entries invoiced is greater than 24 hours. In the image there is a value of 300:48:00 and when I interogate the invoice that is 300 hours 48 mins.
Further complication, but should change any solution, is that I have an historic csv table up to 2020 and then append a 2020 csv from sharepoint so that the data will continue to grow and also reduce reporting out time from the source.
What I have been doing is creating a new column for decimal hours
I do know that to convert the hh:mm format to decimal in Power BI using csv I need "24 * [time]". Also if it is in xlsx format I need "24 * [time] - 24" to get a created column for decimal time. And decimal time will help with visuals and measures.
Question: What do I need to do in this case where I have a column with two time formats in the csv to get a single decimal time column?
In query editor. Select the column and split it by deliminator
You get two columns. 1 is hours, 1 is mins
New column = hours + (mins/60) = decimal number and works with values over 24 hours.
KISS
20 Replies
You should use the Duration functions in M (Query Editor / Power Query)
Here is a link that desribes each of the fuctions. Note that they all should accept a time value as input.
Duration.TotalMinutes( Duration.From( [Time Column] ) ) will convert the entire time duration into minutes. 2 hours 3 minutes = 123 minutes.
I'm not sure the exact output you're looking for, but I would do all of your modeling in Query Editor to have a single, consistently formatted column for Power BI to work with.
Hope this helps,
~ Chris
Thanks for the guidance. And I will work further with this idea.
However when I close the query editor I get a column that is in a decimal looking format. Should I then do a DAX measure to format it in to hours etc?
No link?
Pmorg73 , sorry about that! Here's the link:
https://docs.microsoft.com/en-us/powerquery-m/duration-functions
What are you looking to calculate from that column? Do you need an integer column with the total number of minutes? A decimal column with the total number of hours (with the minutes portion making up the decimal amount)?
Whatever you do, don't create a calculated column in DAX on top of what you're building in M...that will bloat the model.
Let me know how you need the final column formatted, and sample calculations done with that column, and I'll help you write the M.
Cheers,
~ Chris
In Dax you also have a format function :
refer : https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings
See if this helps. You should be able to determine the format of your column by counting the number of colons.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
now I am compleley lost......
where do I put ? I see nowehere to do this
Duration.TotalHours( Duration.From( [Time Column] ) )
last one for a Sunday. Its time to stop haha
two images attached. I interogated the csv file for one specific case. Where ever my entry is greater than 24 hours it is completely and utterly wrong. In query editor I changed the format to duration. That is all I did as I dont follow your other comments and how to apply the DAX line you stated. (???)
I had interogated my csv file. My issue is I have two different formats in my output. Once the output value is large than 24 hours it get confused. So my issue is how todeal with two different formats that are time based?
OK, so the basic problem is that you have a column with different formats that you need to handle different ways. So, you need to identify which format each row is in so that you can later handle it differently. So, I would suggest adding these two columns to your Power Query:
ReplaceColons =Text.Replace([Duration],":","") NumberOfColons =Text.Length([Duration]) - Text.Length([ReplaceColons])Here Duration is your column with your Duration. Again, this is Power Query code. So, now you have your identifier.
