Making the Time in to same format

Hi Dora,

Check this:

I have imported part of your data as sample:

1. Convert the second Transaction Time column to text type. Then Add a new custom column to format the values under second Time column to 6 digits with expressions like:

if Text.Length([Transaction Time.1])=5 then "0"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=4 then "00"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=3 then "000"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=2 then "0000"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=1 then "00000"&Text.From([Transaction Time.1]) 
else [Transaction Time.1]

2. Add another new custom column and use Text.Middle() function to get the desired time format.

Text.Middle(Text.From([New Time]),0,2) & ":" &
Text.Middle(Text.From([New Time]),2,2) &":" &
Text.Middle(Text.From([New Time]),4,2)

3. Simply convert this new Result Column to Time type.

The entire Power Query is:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Amount", type number}, {"Transaction Date", type date}, {"Transaction Date_1", Int64.Type}, {"Transaction Time", type text}, {"Transaction Time_2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Transaction Time_2", "Transaction Time.1"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Transaction Time.1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "New Time", each if Text.Length([Transaction Time.1])=5 then "0"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=4 then "00"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=3 then "000"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=2 then "0000"&Text.From([Transaction Time.1]) 
else if Text.Length([Transaction Time.1])=1 then "00000"&Text.From([Transaction Time.1]) 
else [Transaction Time.1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each Text.Middle(Text.From([New Time]),0,2) & ":" &
Text.Middle(Text.From([New Time]),2,2) &":" &
Text.Middle(Text.From([New Time]),4,2)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Result", type time}})
in
    #"Changed Type2"

Thanks,
Xi Jin.