How to use Find/Left/Right functions to extract time

[Anonymous]

Hello! 

The data source that I am using imports times as text instead of the number of minutes. Is there anyway I can use any of the functions listed above to show the # of minutes?

Thank you!!!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

PattemManohar's suggestion is very helpful, you may try it in Edit Queries.

Of course, you could also use the function you mentioned to achieve your requirement.

To let you understand more, i create calculated columns step by step.

Column1 = FIND("min",[time],1)

Column 2 = LEFT([time],2)

Column 3 = RIGHT([time],6)

Column 4 = LEFT([Column 3],2)

Column5 = IF([Column1]>4,[Column 2]*60+[Column 4],[Column 2])

Note you need transform [Column1],[Column2],[Column4] to type whole number

Or you could nest columns 

co2 = LEFT([time],2)

co4 = var co3 = RIGHT([time],6) return LEFT(co3,2)

time tranform = var co1 = FIND("min",[time],1)
return IF(co1>4,[co2]*60+[co4],[co2])

Best Regards

Maggie

[PattemManohar]

@Anonymous Please use below in "Advanced Editor" in "Power Query Editor"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHIzcxTitWJVjKDs4yM4UwTczjTUCGjSAEhY2yEUA+SgXJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Time]){1}?, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Time.1", "Time.2", "Time.3", "Time.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time.1", Int64.Type}, {"Time.2", type text}, {"Time.3", Int64.Type}, {"Time.4", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Time3New", each if [Time.3] = null then [Time.1] else [Time.3]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "HrToMin", each if [Time.2]="hr" then [Time.1]*60 else 0),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Time.1", "Time.2", "Time.3", "Time.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Time3New", "Min"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "TotalMin", each [Min]+[HrToMin])
in
    #"Added Custom1"