Super Weird Time Column

[Thigs]

Hi all, 

I have some data from a client with a time column, only it is strange. Basically, it goes HHMMSS, however, if the beginning digits are zero, they disappear. EX - if the time should be 012233, it instead shows 12233. If it should be 001122, it shows 1122. So I'm not sure how to convert this. I'm trying a text.end() in Power Query, but not sure it'll work the way I want it to. 

Here's what I'm trying, but it's saying it can't find the name "Time" (and I checked, that's what the column is called) - 

Custom Time = 

if Text.Length([Time]) = 6 then [Time] else
if Text.Length(Time) = 5 then Text.Combine({"0",[Time]}) else
if Text.Length(Time) = 4 then Text.Combine({"00",[Time]}) else
if Text.Length(Time) = 3 then Text.Combine({"000",[Time]}) else
if Text.Length(Time) = 2 then Text.Combine({"0000",[Time]}) else
if Text.Length(Time) = 1 then Text.Combine({"00000",[Time]}) else
"Help"

[daXtreme]

They disappear because PQ wants to treat them automatically as numbers. Just remove the automatic type recognition piece from the code in the right pane (where the steps are defined) and set your own types for all the columns. For this column the type should be 'text', obviously. There's no need to type any fancy formulas to 'regain' the zeros.

[Thigs]

You mean the first step where it says source? I already tried that, it isn't working. I changed the original step to make it text, not numeric, it still isn't happy. 

[daXtreme]

First, make sure the column from the source is imported into PQ as text, not a number. I don't know what you import this from but if this is, say, SQL Server, just make sure this column in the table/view is really text (varchar/nvarchar).

[OwenAuger · Accepted solution]

Hi @Thigs 

First off, it looks like you are missing the square brackets on some of your references to the Time column, which is likely the cause of the error.

Secondly, you can simplify the code by using Text.PadStart.

The code in the Custom Column dialog box would look like:

Custom Time
= Text.PadStart( [Time], 6, "0" )

I'm assuming Time is a text column by the way.

Regards,

Owen

[Thigs]

This worked! Thanks a bunch, I hadn't heard of that formula but it worked perfectly!