Power Query number conversion to hh:mm

[iosiflupis]

Hello all,

I am working in PQ to transform over 40 .csv files to excel. One of the steps that I am trying to accomplish is to convert a whole number, of up to 4 digits, to a time in hh:mm format. I have looked high and low in google search and cannot find anything that comes close.

I have tried the "ChangeType" function and all I get is ERROR. I believe that this will be a multi-step process as I will have to change all numbers to have leading zeros, and then somehow change the format to a "time" format. In the picture, you can see that I will have to do this four times total.

Thank you for your assistance.

Joseph

 

[Jimmypop]

Maybe something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Column1", type text}}, "en-ZA"), "Column1", Splitter.SplitTextByPositions({0, 2}, true), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
    #"Inserted Literal" = Table.AddColumn(#"Changed Type1", "Literal", each ":", type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Literal",{"Column1.1", "Literal", "Column1.2"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Reordered Columns", "Merged", each Text.Combine({Text.From([Column1.1], "en-ZA"), [Literal], Text.From([Column1.2], "en-ZA")}, ""), type text),
    #"Inserted Parsed Time" = Table.AddColumn(#"Inserted Merged Column", "Parse", each Time.From(DateTimeZone.From([Merged])), type time)
in
    #"Inserted Parsed Time"

 

[Jimmypop]

I just did this on a single column on a test sheet... (could not copy your data from a picture)

What I did is I split the column by position (2 positions from right).

This then gave me two columns of where I inserted a new column with ":"....

I then merged the column to give for instance "10:11"... (delimiter was no spaces)

I then changed the data type to Time Only....

 

[JGordon11]

another example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMyUIrViVYyNDcxBTOMjA2hDAMIDSRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), 
                let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
                
    tbl = Table.TransformColumns(Source, {{"Column1", each let t = Text.PadStart(Text.From(_),4,"0") in Time.From(Text.Start(t,2) & ":" & Text.End(t, 2))}})
in
    tbl

 

[iosiflupis]

Jimmypop and JGordon11,

Thank you both for the answers. I will get on them as soon as I get back to the data.