Forum Discussion
Solved
Convert text without consistent formatting to duration
In Power BI, I have a text column that contains what should be a duration, only it's in text format so I can not format it as a 'duration'. Examples are: "28 Hours", "5 Hours 20 Minutes", "1 Hour", "...
The #"Added Custom" step is the key one in the M provided. First convert your text column to lower case using that option on Transform/Format. Then on the Add Column tab, choose Custom Column and paste in the pop-up window.
let hmslist = {{"hour", 24}, {"minute", 24*60}, {"second", 24*60*60}}, splittext = Text.Split([Result.u_time_actual_time], " "), partialcombine = List.Transform(List.Split(splittext, 2), each Text.Combine(_)), hms = let input = partialcombine in List.Transform(hmslist, (w)=> try Number.From(Text.Select(List.Select(input, (y)=> Text.Contains(y, w{0})){0}, {"0".."9"})) * (1/ w{1}) otherwise 0), result = List.Sum(hms) in resultPat
Thank you! Can you tell me how I can add a column to my existing table in power query? The column which contains the text is 'Result.u_time_actual_time'. Thanks!
The #"Added Custom" step is the key one in the M provided. First convert your text column to lower case using that option on Transform/Format. Then on the Add Column tab, choose Custom Column and paste in the pop-up window.
let
hmslist = {{"hour", 24}, {"minute", 24*60}, {"second", 24*60*60}},
splittext = Text.Split([Result.u_time_actual_time], " "),
partialcombine = List.Transform(List.Split(splittext, 2), each Text.Combine(_)),
hms = let input = partialcombine in List.Transform(hmslist, (w)=> try Number.From(Text.Select(List.Select(input, (y)=> Text.Contains(y, w{0})){0}, {"0".."9"})) * (1/ w{1}) otherwise 0),
result = List.Sum(hms)
in
result
Pat