Column: Add leading zeros left and right of hyphen

[pbeeyeqs]

I have a text column in PowerBI, with numeric digits separated by a hyphen. I need the left side to be exactly 5 digits. If it is less, then add leading zeros. The right side needs to be 4 digits. Any less, add leading zeros.

For example:

• 0002-800 -> 00002-0800
• 0001-0800 -> 00001-0800
• 12345-220 -> 12345-0220

Any help is appreciated. Thanks

[v-yulgu-msft · Accepted solution]

Hi @pbeeyeqs,

Based on Vvelarde's suggestion, I have something to add。 Before adding the custom column, please remove the "Change type" step after spliting column.

Then, you can paste the provided code into "add custom column" box.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMNK1MDBQitUBcwx1DWA8QyNjE1NdIyMgLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"ID.1", "ID.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Repeat("0",5-Text.Length([ID.1])) & [ID.1] & "-" &
    Text.Repeat("0",4-Text.Length([ID.2])) & [ID.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID.1", "ID.2"})
in
    #"Removed Columns"

Best regards,

Yuliana Gu

[Vvelarde]

@pbeeyeqs 

Hi, you can do with Query Editor

Step 1: Select your column and Split it by Delimiter.

Step 2: Add a Custom Column : (ID.1 y ID.2 is the splitted columns).

Text.Repeat("0",5-Text.Length([ID.1])) & [ID.1] & "-" &
 Text.Repeat("0",4-Text.Length([ID.2])) & [ID.2]

Step 3: Remove other columns (ID1 and ID2)

Regards

Victor