Power Platform Community Forum Thread Details

Padding a number at beginning and end

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:

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}})

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}})

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.

Quick Links

Leaderboard > Power Apps - Power Query