Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
I'd like to concatenate and fill zero in a data using in power query, like the example below.
Column 1 Column 2 Column 3
45985381 1 8
5250111 2 9
328111 4 10
Changed
New Column
45985371000108
05250111000209
00328111000410
Thanks,
Solved! Go to Solution.
Use the Pad and CombineColumns funtions
Text.PadStart(Text.From([Column1], "en-GB"), 8, "0")
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjG1tDA1tjBUgAAlHSVDILZQitWJVjI1MjUwNATxjYDYEixmbGQBETIBKTVQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
Type = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
Pad1 = Table.AddColumn(Type, "Pad1", each Text.PadStart(Text.From([Column1], "en-GB"), 8, "0"), type text),
Pad2 = Table.AddColumn(Pad1, "Pad2", each Text.PadStart(Text.From([Column2], "en-GB"), 4, "0"), type text),
Pad3 = Table.AddColumn(Pad2, "Pad3", each Text.PadStart(Text.From([Column3], "en-GB"), 2, "0"), type text),
#"Merged Columns" = Table.CombineColumns(Pad3,{"Pad1", "Pad2", "Pad3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Also if you not sure of how to write a function try Add Column From Examples. This has a go at guessing what your want and writes the functions for you.
https://docs.microsoft.com/en-us/power-bi/desktop-add-column-from-example
Use the Pad and CombineColumns funtions
Text.PadStart(Text.From([Column1], "en-GB"), 8, "0")
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjG1tDA1tjBUgAAlHSVDILZQitWJVjI1MjUwNATxjYDYEixmbGQBETIBKTVQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
Type = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
Pad1 = Table.AddColumn(Type, "Pad1", each Text.PadStart(Text.From([Column1], "en-GB"), 8, "0"), type text),
Pad2 = Table.AddColumn(Pad1, "Pad2", each Text.PadStart(Text.From([Column2], "en-GB"), 4, "0"), type text),
Pad3 = Table.AddColumn(Pad2, "Pad3", each Text.PadStart(Text.From([Column3], "en-GB"), 2, "0"), type text),
#"Merged Columns" = Table.CombineColumns(Pad3,{"Pad1", "Pad2", "Pad3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Also if you not sure of how to write a function try Add Column From Examples. This has a go at guessing what your want and writes the functions for you.
https://docs.microsoft.com/en-us/power-bi/desktop-add-column-from-example
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |