Power Query: Parsing HH:MM:SS durations that are greater than 24 hours

Question

I'm trying to Query data from another data source in Excel, and am receiving parsing errors for any durations larger than 24:00:00.

Expression.Error: We couldn't parse the Duration literal. Details: 51:33:08

How do I correctly have excel parse durations such as 128:22:13 as a duration?

Edit:

The data is currently text in HH:MM:SS format. I am using the Excel Query Tools to import data from an external source. There is no code, and I am not trying to convert cells in a worksheet. These conversions are part of the Query Tools:

For someone with 1K+ reputation, I would expect you would know how to ask a question on stackoverflow better than that by now even if you are in a hurry. You can convert it to text by adding `'` in front or convert it to a number in the query. — Slai, Aug 11 '16 at 22:40
@xidgel There is no parsing code, I would post it if there was. It's "automatic" parsing using the Excel Query tools. — Douglas Gaskell, Aug 11 '16 at 22:42
@Slai Please don't be unnecessarily rude. It is currently text, in `HH:MM:SS` format, that needs to be parsed into a duration with the Query Tools. These are not cells I am editing. I have added more details. — Douglas Gaskell, Aug 11 '16 at 22:44
@xidgel Thanks, I didn't even know this was Power Query. I found it from "Microsoft Query" — Douglas Gaskell, Aug 11 '16 at 23:33
@DouglasGaskell BTW if you click that little (?) in the top right there's some more help pages. If you know how to program you can get a lot done with Power Query and the "M" programming language! — Carl Walsh, Aug 12 '16 at 07:27

score 11 · Answer 1 · answered Aug 12 '16 at 07:16

11

We have a Duration.FromText library function but that's documented to only allow between 0 and 23 hours. This function is what's getting applied under the hood when you change type from text to duration.

Luckily the #duration constructor has no such restriction, so here's a simple little parsing function you can use.

let
    DurationFromYourText = (text as text) as duration => let
        Split = Text.Split(text, ":"),
        AsNumbers = List.Transform(Split, Number.FromText),
        Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2})
    in
        Duration,
    Invoked = DurationFromYourText("128:22:13")
in
    Invoked

answered Aug 12 '16 at 07:16

Carl Walsh

6,100
2
46
50

Thanks Carl. I'm new to Excel, but not to coding, where would I actually put this code in Excel to be able to use it in Power Query? Or where would I go to add a new "type" to the Power query. – Douglas Gaskell Aug 12 '16 at 17:42
2

@DouglasGaskell You can paste in this function into the Advanced Editor (be careful about commas as we have a strict syntax). Then you could click Add Custom Column with `= DurationFromYourText([Total Duration])`. If you google how to create custom Power Query / M functions there's plenty of blog posts that walk through in more detail than I can. Maybe http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ is a good one to start with? – Carl Walsh Aug 12 '16 at 18:22
@DouglasGaskell here's a quick-start of M that might be more helpful to learn the "M" query language than the Excel-focused blog posts: https://msdn.microsoft.com/en-us/library/mt270235.aspx – Carl Walsh Aug 12 '16 at 18:24

score 0 · Answer 2 · answered Aug 11 '16 at 23:07

You can split the text into two or more columns https://support.office.com/en-us/article/Split-a-column-of-text-Power-Query-5282d425-6dd0-46ca-95bf-8e0da9539662#__toc354843579

After that you can divide the hours column by 24 to get the days, and modulus 24 to get the remaining hours. For the mod, you might have to change some of the Power Query Formula Language M https://msdn.microsoft.com/en-us/library/mt253344.aspx

Power Query: Parsing HH:MM:SS durations that are greater than 24 hours

2 Answers2

Linked