Handwritten PQ query not working

Power Query
1

Hi @sam.mckay
I’m writing my first handwritten PQ function to convert a string like “2 01:10:00” in 49:10:00, that is convert the first token representing days in hours added to the hours of the second token, or when the string is just one token like “10:05:00” no change required.
The function is straightforward but something is happening under the hood and I cannot understand what is wrong.

Thanks for your valuable help

Roberto

(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 = Number.ToText(Number.FromText(Hours) + ConvertedDayInHours) & ":" & Minutes & ":" & Seconds
in    
    Result

TimeTransformation.pbix (13.8 KB)

1 Like
2

Hi @Roberto,

Your variable HMS was accessing the 2nd element in a list which isn’t present when there’s no day element in the text string, added a conditional test to switch between getting a first or second element from the list to resolve it.

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

I hope this is helpful

4 Likes
3

Hi @Melissa
thanks so much!

Roberto

1 Like