Simple text transformation handmade query error

Power Query
1

Hi @sam.mckay,

I’m writing a simple function in PQ which is meant to transform a string in the format “2 01:40:00” in 49:40:00, or “12:30:00” in “12:30:00”.
Essentially if the string is formed by 2 tokens separated by a space the first is the number of days that has to be converted in hours and summed up to the hours of the second token. If only 1 token (12:30:00) no action.
There is some automatic thing going on when I try to run it, but I cannot understand where I’m wrong.

Thanks for your valuable support

Roberto

Expression.Error: We cannot convert the value #time(12, 12, 13) to type Text.
Details:
Value=12:12:13 PM
Type=[Type]

= (TimeString as text) as text =>
    let
        Tokens = Text.Split(TimeString, " "),
        ConvertedDayInHours = if List.Count(Tokens) = 1 then 0 else Number.FromText(Tokens{0}) * 24,
        HMS = Text.Split(Tokens{1}, ":"),
        Hours = HMS{0},
        Minutes = HMS{1},
        Seconds = HMS{2},
        Result = Text.Combine( Number.ToText( Number.FromText(Hours) + ConvertedDayInHours ), Minutes, Seconds, ":")
    in    
        Result

TimeTransformation.pbix (13.8 KB)

2

@Roberto Try this:

( TimeString as text ) as text =>
    let
        Tokens = Text.SplitAny ( TimeString, " :" ),
        HasDay = Text.Contains ( TimeString, " " ),
        SeparatedList =
            if HasDay then
                Tokens
            else
                { null } & Tokens,
        ConvertedDayInHours =
            if SeparatedList{0} = null then
                0
            else
                Number.FromText ( SeparatedList{0} ) * 24,
        Hours = SeparatedList{1},
        Minutes = SeparatedList{2},
        Seconds = SeparatedList{3},
        Result = Number.ToText (
            Number.FromText ( Hours ) + ConvertedDayInHours
        )
            & ":"
            & Minutes
            & ":"
            & Seconds
    in
        Result
2 Likes
3

Hi @AntrikshSharma ,
it worked nicely (I had no doubt :nerd_face:), but I didn’t get why mine did not.
When trying to launch with the parameter 12:12:13 I get this conversion error message.
It seems PQ is trying to ease and please the user by adding some automatic type-ing (in this case it got it’s a time).
image

I’ve also changed the code just a little to let the function work even when the parameter is not provided. What is the best way to manager blank, space and null values w/o adding a new transformation step in the ETL?