Power Query - Table.TranformColumns

I'm trying to add leading zeros into a column in power query call JobCodes. I know I can do this by adding a new column using Text.Start([JobCodes],5,"0"), but I don't want to add a new column and go back to remove the column I don't need. I want to be able to do this in one step using Table.TransformColumns function. Is this possible?

Code:

Table.TransformColumns(#"Changed Type", each Text.PadStart([JobCodes],5,"0"))

Error:

Expression.Error: We cannot convert a value of type Function to type List. Details: Value=Function Type=Type

3 Answers

Your syntax is just a bit off.

I think this is what you want:

= Table.TransformColumns(#"Changed Type",{{"JobCodes", each Text.PadStart(_, 5,"0")}})

The error is because it was expecting a list of columns that you want to transform (notice the {{...}} above.

The easiest wat to get the syntax right is to use the GUI to do a transformation and then just edit the function a bit. For example, you could use Format > Add Prefix which would give you the following step (assuming you choose to prefix 000).

= Table.TransformColumns(#"Changed Type", {{"JobCodes", each "000" & _, type text}})

Just take out the "000" & _ and put in the transformation you actually want.

Or

= Table.ReplaceValue( Source, each [JobCodes] ,each Text.PadStart(Number.ToText([JobCodes]),5,"0") ,Replacer.ReplaceValue,{"JobCodes"})
in Replace

The correct expresion is:

= Table.TransformColumns(#"Changed Type",{{"JobCodes", each Text.PadStart(Text.From( _), 6, "0"), type text}})