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
1719
1721
1722
1726
1730


Through this formula.

 

=IZQUIERDA(B2;LARGO(B2)-2) & ":" & DERECHA(B2;2)


I am now trying to set up in power query a command in order to transform the same time data In order to get the proper format. Can anyone help?

 

Thank you very much in advance, Juan

 

 

=IZQUIERDA(B2;LARGO(B2)-2) & ":" & DERECHA(B2;2)

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

16 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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])

    • juan jimenez's avatar
      juan jimenez
      Iron Contributor
      Jan 25, 2020

      SergeiBaklan 

       

      Thanks!!!

       

      I am learning a lot.

       

      Enclosed please find my version of your suggestions and the source data (normally comes in a .txt file but as this web does not accept txt files i have converted into xls).

       

      I still have problems with 1 digit single AM times. look on the 215 and also with 24:00

       

      Can you help me with these 2 errors?

       

      Thank you again

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 25, 2020

        juan jimenez 

        You are welcome.

        Sorry, but I didn't find the source file. Query takes data from horario.txt which was not attached. 

  • 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