power query and time format

juan jimenez 

Another variant, and if more close to the formula you used in Excel

the script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TrimmText = Table.TransformColumns(
        Table.TransformColumnTypes(
            Source,
            {{"A", type text}}, "en-GB"
        ),
        {{"A", Text.Trim, type text}}
    ),
    CleanText = Table.TransformColumns(
        Table.TransformColumnTypes(
            TrimmText,
            {{"A", type text}}, "en-GB"
        ),
        {{"A", Text.Clean, type text}}
    ),
    ConvertToTime = Table.ReplaceValue(
        CleanText,
        each [A] ,
        each Time.FromText(
                Text.Start([A],2) & Text.End([A],2)
              ),
        Replacer.ReplaceValue,{"A"}
    )
in
    ConvertToTime

If source data is more clean that could be simply = Time.FromText([A])