power query and time format

[juan jimenez]

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)

[#2]

@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"

 

[#3 · Accepted solution]

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

[#4]

@Sergei Baklan 

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

[#5]

@juan jimenez 

You are welcome.

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

[#6]

I think i attached an excel file called horario.xlsx as txt is not accepted

[#7]

@juan jimenez 

I see, thank you.

Power Query converts 4 characters text into the time, it has no guess what it could be if less characters. I's add each value with zeros if it less than 4 characters:

= Table.ReplaceValue(#"Tipo cambiado1",each [Column1.1], each Text.PadEnd([Column1.1],4,"0"),Replacer.ReplaceText,{"Column1.1"})

As for 2400 Power Query doesn't recognise it as correct time, actually that is 00:00 next day. We may consider that as the bug or as feature, but that's what we have. Thus we may replace 2400 on 0000, or on 23:59 to have more or less correct result

= Table.ReplaceValue(#"Replaced Value","2400","2359",Replacer.ReplaceText,{"Column1.1"})

Please check in attached file, source file location is to be adjusted.

[#8]

@Sergei Baklan 

Would it be possible to add each value with zeros if it less than 4 characters, but the zero at the end?

If you added it at the end, 9am, would be 90:00 instead of 09:00

thank you again.

[#9]

@juan jimenez 

I guess you mean to add zeros in front of text, it's more logical. With that 900 will be 0900. When it hall be Text.PadStart instead of Text.PadEnd

= Table.ReplaceValue(#"Tipo cambiado1",each [Column1.1], each Text.PadStart([Column1.1],4,"0"),Replacer.ReplaceText,{"Column1.1"})

[#10]

@Sergei Baklan 

it works! tx

Can you also help me to divide a column by ITS FIRST COMMA (",")? 

#"Dividir columna por delimitador" = Table.SplitColumn(#"Valor reemplazado83", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),

This is because I sometimes  get lines with more than the expected first coma

1220, XXX.aaa.bbb.ccc.ddd,eee

I would like just to divide the column in the beginning by the first comma (",") appearing.

Thanks, juan

[#11]

@juan jimenez 

Juan, splitting the column just select Left-most delimiter instead of default "each occurrence"

Result is

[#12]

@Sergei Baklan 

Thank you again for the help. it works!

The problem now is that I don't know where i was wrong the the commands as once  the excel is opened, it's start running and consuming CPU until the laptop blocks.

Could you have a look on the attached excel?

Please note that as I have so many "replace" commands, I have just copied the first and last command of each series of "replace" commands

[#13]

@juan jimenez 

It's hard to analyse the query in such format. Is it possible to attach the Excel file with the latest query and perhaps sample of source text file with more realistic data?

[#14]

@Sergei Baklan 

Dear Sergei,

You have helped me a lot on this topic. 

However if I am going to send you some documents with private information I wonder if I could send you any other way privately.

Thank again, Juan

[#15]

@juan jimenez 

Hi Juan,

Please send me a private message here, will contact through it. The only I don't promise quick answer.

[#16]

hi,

i get this messeage "You have reached the limit for number of private messages that you can send for now. Please try again later."

and i have sent just a few messages over the last 2 years....

[#17]

@juan jimenez 

That's strange. Please ask techcommunity@microsoft.com to sort this out.