Padding a number at beginning and end

[Syndicate_Admin]

Hi all,

I have been tasked with formatting data for import into a legacy system that requires the values in any given field to be a fixed length.  This is particular challenge with the number fields because they need 11 characters in front of the decimal and 4 after.  Thus 1.23 needs to be converted to 00000000001.2300.

I will also say that I have been working with Power Query for about 24 hours so if there is a better method than what I have in mind I would love to hear it.

My plan is:

1

Use Text.BeforeDelimiter to extract the value prior to the "."

Use Text.PadStart to add the required leading 0's

Table.TransformColumns(#"Navigation 1", {{"cr6e3_number1", each Text.PadStart(Text.BeforeDelimiter(Text.From(_), ".", 0))11,"0", type text}})

2

Use Text.AfterDelimiter to extract the value after the "."

Use Text.PadEnd to add the required following 0's

Table.TransformColumns(#"Navigation 1", {{"cr6e3_number1", each Text.PadEnd.(Text.AfterDelimiter(Text.From(_), ".", 0),4,"0"), type text}})

3

Combine 1, . and 3.

I can add the decimal to the end of 1 using Text.Insert.  I don't know how to add 2 at the end.

[Vijay_A_Verma · Accepted solution]

Assuming, your column is named Data, use below formula

= Text.PadStart(Text.BeforeDelimiter(Text.From([Data]),"."),11,"0")&"."&Text.PadEnd(Text.AfterDelimiter(Text.From([Data]),"."),4,"0")

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMlaK1QEyjIxN9CwszcEcc0tjM0tjcz1DMM/YxEQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.PadStart(Text.BeforeDelimiter(Text.From([Data]),"."),11,"0")&"."&Text.PadEnd(Text.AfterDelimiter(Text.From([Data]),"."),4,"0"))
in
    #"Added Custom"