Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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.
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
[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. (???)
In Dax you also have a format function :
refer : https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings
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?
@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
@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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |