Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pmorg73
Post Patron
Post Patron

Time format export data from hours to decimal

formatting issue.png

 

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?

1 ACCEPTED SOLUTION

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

View solution in original post

20 REPLIES 20
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Just as I am about to try this. What format should I leave the [duration] column in? 

I deleted all of the queries and started again. When I import my csv file I get the column as Time format (clock face) AM/PM

 

I have tracked through one known invoice task to see what may be going on.

 

I have a Workflowmax invoice with a task that has a summed time of 15:18 (hh:mm). so 15 hours and 18 mins of timesheet entries summed up against an invoiced $ value. (Will be doing average hourly rate later etc)

 

In the csv file the entry is ",13:18,"    Randomly looking at my csv they are all in that format as far as I can see

 

Now when freshly imported to BI it thinks its a time of 3:18:00PM

 

I would like this as a decimal hours. I know I have some invoices with task sums of up to 300 hours

 

In Excel it was a fix of  (24 * TIME = decimal number) but that was with it converted in to a table first.

 

In my data silo version of this (using an excel table) I made a column to do this step but made the column a DATE/TIME format after advice from this forum. It has worked well for 6 months like this. But I am rebuilding using csv for sharepoint auto updating so going this problem again haha.

 

[edit] And another thing. The direct csv import is in Time format as described, but the sharepoint csv link is in Text format.

 

Thoughts?

I have uploaded the BI file with limited data in it.

 

The report has 2 tables and 2 queries. "06 cut" was a direct csv import. query1 was a sharepoint link where I deliberately had a value greater than 24 hours in it. This is where I think Power BI get confused.  data and BI file  

https://batchelarmcdougall-my.sharepoint.com/:f:/g/personal/phil_bmconsult_co_nz/ErYJ-1YpWJpMqS8V3oa...

 

[edit] - I have decided to get rid of the two imports and do everything from sharepoint. i hope this will then result in a single combined table straight away and only one format applied to the column. I will update once I have tried this. (I am busy doing my day job at the same time)

to come back to this thread. 

 

For entries that are less than 24 hours 24:00 the following works. 

 

import the data as a combined sharepoint csv load. 

change column format to Date/Time

Make a new column = 24 * [time column]

 

you get a column of decimal values.

 

HOWEVER: If the entry is 24:00 exactly or greater it falls over and BI will not parse it.

In my data set I currently have 23 entries that are like this. I do not see a way for me to fix these 23 errors. Any help greatly appreciated

https://community.powerbi.com/t5/Desktop/Problems-with-hours-greater-than-24-hours/td-p/112680

 

I think I need a combination of this thread, plus the good advice about using text.length.

 

My problem boils down to (I think) that I have a column that can have up to ###:00 hours. I dont believe I have greater than 500 hours in any one cell.

So I have a removed Colons column. Then text.length that column.

From that I can work out how many digits are hours (I hope) to the left of the colon and the use the other thread solution. I will update how this goes.

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

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. (???)

 

hours 1.pnghours 2.png

amitchandak
Super User
Super User

Anonymous
Not applicable

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

No link?

Anonymous
Not applicable

@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

Anonymous
Not applicable

@Pmorg73 , I reread your initial post.

 

Try this:

 

Duration.TotalHours( Duration.From( [Time Column] ) )

 

That should make 1 hr 45 min return as 1.75  

I dont know where to put that string?

crossing threads a bit but back to your earlier post.

 

What I actually want is a column that has up until now been in decimal hours.  so 2.5 hours etc. 

 

i think it would still work as hh:mm. But I am getting lost when I have done what you said. My column is now a duration in power query. When I close the editor the first entry is 11 hours and my table value is 0.45

So that looks to be in a day format. How do I change this. Note I am using a roll back to Dec version as Febs is no good for csv imports

playing further in query editor

 

I made it a duration as discussed. I just went to transform and clicked on make sum of hours.......returns a decimal value of each row that at first glance looks correct......this might be my solution. will just interogate

no its incorrect values. not the solution. 109hours output turned in to 13.3 hours so its miles off

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.