Forum Discussion
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:
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.
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"
1 Reply
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"