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=FunctionType=Type
Best Answer
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 ReplaceThe correct expresion is:
= Table.TransformColumns(#"Changed Type",{{"JobCodes", each Text.PadStart(Text.From( _), 6, "0"), type text}})