Forum Discussion
Add leading zero to a column value and concatenate with other value in place.
- 4 years ago
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
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
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_Query4 years ago
Helper II
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.