Adding new column with text starting with and x length long

jilbobagins

Member
Joined
Apr 11, 2019
Messages
80
Reaction score
0
Points
6
Excel Version(s)
2016
Howdi,

If I wanted to add a new custom column with the text from another column starting with "CR" for example (Below) and a certain then a certain string length eg CR00012345 (so 10 long).

Thanks in advance
 
With these values in an Excel Table named Table1:
Code:
OrigVal
      1
     12
    123

  66778
1234567


This M-Code creates a new column that prepends "CR" and enough zeros to make a 10 character field value:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrigVal", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NewVal", each "CR" & Text.End("0000000000" & Text.From([OrigVal]),8))
in
    #"Added Custom"
These are the results:
Code:
OrigVal       NewVal
      1        CR00000001
     12        CR00000012
    123        CR00000123
       
  66778        CR00066778
1234567        CR01234567

Is that something you can work with?
 
Back
Top