Forum Discussion

Thigs's avatar
Thigs
Icon for Helper IV rankHelper IV
4 years ago
Solved

Super Weird Time Column

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 01223...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
Thigs
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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).