Forum Discussion
Time to Duration conversion throws an error.
Forum didn't accept screen shot in my original reply, and would not let me edit it after it was submitted, so here is the sample input. Hopefully it woks this time.
I have the exact same problem.
Did you find anything that worked?
Thank you
J
BAWardbbrauer100Anonymous You need to ensure the format of the cells is "Text" first. This can be done either in Excel by highlighting the cells -> Format Cells -> Text, or in Power BI Query Editor by highlighting the column and going to the ribbon -> Transform -> Data Type -> Text. Then you can add a "calculated column" in Power BI Query Editor using the following:
CustomColumn = Duration.FromText([Duration])
You can then extract your desired duration from that new custom column such as: total minutes, total hours, total seconds, etc.
In my experience, I've found the easiest way to use duration is by subtracting an end time from a start time and then converting it to the desired duration. Hope this helps, thanks.
Converted to text a few times in the PQ editor. Didn't work in any scenario. Tried each of the following and all results in Duration errors:
1. Duplicated values column, changed format to Text
2. Added a column with MCode using Duration.From
3. Added a column with MCode using Duration.FromText
all produced the same.But if it is converted to text how can it then be used on graphs?
Say, for instance, where one wants to graph total amount of time spent on a project?
Anonymous,
Well in that case, you would have start times and end times right? The scenario that was presented here was just a table of durations.
Ideally, if you were tracking the progress of a project you would start off with a start date/time and end date/time. These would be considered as DateTime values in Power BI.
From the query editor, you would be able to calculate the duration of the project by doing [End Time] - [Start Time] and then converting that value into the duration you want. Once you get all that data sorted out, then you would use the appropriate visual to display those values.
Possible visuals would be Gannt or horizontal bar chart.
If you can provide me with a sample dataset, I'll see what I can mock up to give you a better idea of how to do it.
