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
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
addCodeText
Output:
Pete
- RVGypsy4 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.
- RVGypsy4 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.