Forum Discussion
RVGypsy
4 years agoNew Member
Delimiter
I have a column of cells with some cells having 5 number and some having 6. None have the correct format that I need them in and all need to be 6 numbers long. I need to add dashes in between so I ha...
- 4 years ago
Ok. You've put the code I provided into a custom column, rather than as a new query with Advanced Editor. The code I provided was just an example of how to perform the steps on your query.
To apply this to your query, you would use this code in a new custom column:
Text.Combine( Splitter.SplitTextByPositions({0, 2, 4}) ( Text.PadStart(Text.From([Code]), 6, "0") ), "-" )Full example query to paste into Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NTZQitUBsowMDM1gLANTKMvUzNgExrS0MAczLYHAEKgpFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}}), addCodeText = Table.AddColumn( chgTypes, "codeText", each Text.Combine( Splitter.SplitTextByPositions({0, 2, 4}) ( Text.PadStart(Text.From([Code]), 6, "0") ), "-" ) ) in addCodeTextOutput:
Pete
RVGypsy
4 years agoNew Member
Ok that is where i get lost as i dont know how to write or what words to use. If it wasnt for power query asking the questions I would be lost. I will study what you have here and see if I can learn this.
RVGypsy
4 years agoNew Member
Don't know exactly what I did bu I got this code to work in the column I needed it in and my sheet now wrks as I intended it to work.