Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Concatenate and Fill in

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,

1 ACCEPTED SOLUTION
stretcharm
Memorable Member
Memorable Member

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

View solution in original post

1 REPLY 1
stretcharm
Memorable Member
Memorable Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.