Add leading zero to a column value and concatenate with other value in place.

[PowerBI_Query]

I want to add leaing zero's if the number lenght is less than 9 digits in Column3 (after adding leading zero it can be in text format to retain the zeros). Then I want to concatenate it with values in Column1.

E.g

Column1   Column2        Column3

12556777  Description1  1347897 

55556777  Description2  1347897

.

.

etc

Output

Column1   Column2        Column3

12556777  Description1  12556777 001347897 

55556777  Description2  55556777 001347897 

.

.

etc

I dont want to Add a column to add zero e.g Number.ToText([Column3], Text.Repeat("0",9)

I have 300 plus columns. The newly added column goes at the end.

After, I merge the merged column also goes at the end. I need to move it to the beginning and remove the Merged and zero added Column3.

The column names get hardcoded when reordered.

Instead can I do the zero addition and concatenation in column3 wihtout adding columns and moving them?

[Vijay_A_Verma · Accepted solution]

Issue this statement where you will need to replace Changed Type with your previous step. 

= Table.ReplaceValue(#"Changed Type",each [Column3], each Text.From([Column1])&Text.PadStart(Text.From([Column3]),9,"0"),Replacer.ReplaceValue,{"Column3"})

I can see a space between concatenated value in your result column. I haven't put a space in above formula. If you need a space, use below formula

= Table.ReplaceValue(#"Changed Type",each [Column3], each Text.From([Column1])&" "&Text.PadStart(Text.From([Column3]),9,"0"),Replacer.ReplaceValue,{"Column3"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNTUzNzdX0lFySS1OLsosKMnMzzMEcg2NTcwtLM2VYnWilUxNsakyQlYVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [Column3], each Text.From([Column1])&Text.PadStart(Text.From([Column3]),9,"0"),Replacer.ReplaceValue,{"Column3"})
in
    Custom1

[PowerBI_Query]

It worked! I need space as delimiter while merging.

Why do your source step has JSON.?

Table1:

Column1  Column12  Column2        Column22   Column3
12556778 fff               Description1   hg              1347897
12556777 fff               Description2   gh              1347897

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [Column3], each Text.From([Column1])&" "&Text.PadStart(Text.From([Column3]),9,"0"),Replacer.ReplaceValue,{"Column3"})
in
    Custom1

Output:

Column1  Column12 Column2       Column22 Column3
12556778 fff              Description1 hg             12556778 001347897
12556777 fff              Description2 gh             12556777 001347897

[AlexisOlson]

The compressed binary JSON format is just how Power Query represents tables in M that are created via the Enter Data button of the GUI. Providing code this way allows others to exactly reproduce the query without depending on unseen data sources (like Excel.CurrentWorkbook in your example).

[PowerBI_Query]

So the JSON code is not linked to any table in excel. After loding I see just the query data loaded in. Thank you for letting me know.