You’re offline. This is a read only version of the page.
25
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.
Share
Report
All responses (
Answers (
