Forum Discussion

juan jimenez's avatar
juan jimenez
Iron Contributor
Jan 22, 2020
Solved

power query and time format

Hi   Some moths ago i managed to get help in order to change a list of time data expressed like:   1502 1515 15 25   1532 1542 1544 1550 1562 16 45 1702 17 06 ...
BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 22, 2020

    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])

ChrisMendoza's avatar
ChrisMendoza
Iron Contributor
Jan 22, 2020

juan jimenez -

 

A couple of extra steps as I'm unsure if your data was formatted with leading/trailing and white-spaces. Also an Error is returned for the value 15:62 as this does not exist in Time.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unformatted Time", type any}}),
    #"Cleaned Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Changed Type", {{"Unformatted Time", type text}}, "en-US"),{{"Unformatted Time", Text.Clean, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Cleaned Text", "Unformatted Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Unformatted Time.1", "Unformatted Time.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Unformatted Time.1", type text}, {"Unformatted Time.2", type text}}, "en-US"),{"Unformatted Time.1", "Unformatted Time.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Added Custom Column" = Table.AddColumn(#"Merged Columns", "Custom", each Text.Combine({Text.Start([Merged], 2), ":", Text.Middle([Merged], 2, 2)}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type time}})
in
    #"Changed Type1"

 

Resources