how to remove specific characters in specific cells

[iosiflupis]

hello all (again),

I have a question. I am trying to construct a power query in excel. I can get most of what I want thru trial and error, youtube searching, and Google searching. However, I am stumped on a problem.

In the attached picture you can see 4 columns highlighted. the first two (departure) are what I currently have. The second two (arrival) are what I can now get to. What I need is to be able to remove the ":" from the blank cells. When I run a power query editor they show up as null. When I split, merge, and change the type to "Time" I get an error. The error appears when I change the type. What can I do to remove only the instances of the ":" in a blank cell?

Thank you.

P.S. if you know of a better way to convert a 4-digit number into 24 hr time without any errors I could use that instead of the long way I am doing it now.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.TransformColumns(Source, {{"Time", each Time.From(Text.PadStart(Text.From(_), 4, "0"))}})
in
    Result

Book2
	A	B	C	D
1	Time		Time
2	2339		11:39:00 PM
3	1651		4:51:00 PM
4	1304		1:04:00 PM
5	1544		3:44:00 PM
6	2048		8:48:00 PM
7	1052		10:52:00 AM
8	245		2:45:00 AM
9	422		4:22:00 AM
10	1039		10:39:00 AM
11	1555		3:55:00 PM
12	627		6:27:00 AM
13	850		8:50:00 AM
14	527		5:27:00 AM
15	517		5:17:00 AM
16	2311		11:11:00 PM
17	1339		1:39:00 PM
18	1841		6:41:00 PM
19	1321		1:21:00 PM
20	1647		4:47:00 PM
21	1150		11:50:00 AM
22	1314		1:14:00 PM
23	1036		10:36:00 AM
24	1904		7:04:00 PM
25	11		12:11:00 AM
26	210		2:10:00 AM
27
Sheet1

 

[iosiflupis]

I am not quite sure where to put this expression. I tried to edit the query in advanced editor but was receiving an error. then I tried to insert a column and couldn't get the expression to pass the error check.

 

[JGordon11]

if you wanted to add a time formatted column use code similar to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "TimeFormatted", each Time.From(Text.PadStart(Text.From(_[Time]), 4, "0")))
in
    Result