Forum Discussion
Delimiter
- 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
Well That ends up like I want it to but not all the way. I need it to pull from the first column and change that number (watever number it may be) into what I need. I currently have 287 rows of diferrent specific number to do this to. starting at 11530 where I need to add a leading zero and the dashes to 999910 where all I need are the dashes.
Hi RVGypsy ,
Can you give me a bit more detail around what part of this isn't working please?
Are there certain values that appear in your first column that this isn't working with, or are you getting errors on some values?
It appears to work fine on the values you've provided above:
If you're able to share the entire list of your first column values I can test my solution on the actual data and fix anything that doesn't work.
Pete