Forum Discussion
Delimiter
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
Hi RVGypsy ,
Paste this code over the default code in Advanced Editor to follow the steps to do this.
I've left the steps separate to see each stage, but you could probably combine these all into one or two steps if you wanted:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMDRTitUBswxMoSxTM2MTGNPSwlwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"number", Int64.Type}}),
padStart = Table.AddColumn(chgTypes, "pad", each Text.PadStart(Text.From([number]), 6, "0")),
splitGroups = Table.SplitColumn(padStart, "pad", Splitter.SplitTextByPositions({0, 2, 4}), {"pad.1", "pad.2", "pad.3"}),
combineGroups = Table.AddColumn(splitGroups, "text", each Text.Combine({[pad.1],[pad.2],[pad.3]}, "-"))
in
combineGroups
Pete
It just gives me repating values instead of matching the value in the first column and transforming it into the text I need.InputOutput
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
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.
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.