Forum Discussion
Excel formula to power query M code
This is fairly complex but it does match the Excel result (when all the letters are upper case). Add a custom column and put the code below in the pop-up box, replacing TextColumn with the name of your text input column.
let input = Text.Upper([TextColumn]), letters = {"A".."Z"}, concatvalues = {null, "A".."Z", "0".."5"}, splitinput = List.Split(Text.ToList(input), 5), lettercheck = List.Transform(splitinput, (x)=> List.Transform(x, (y)=> Number.From(List.Contains(letters, y) ))), multipliers = {1,2,4,8,16}, listsums = List.Transform(lettercheck, (a)=> List.Sum(List.Transform({0..4}, (b)=> a{b}*multipliers{b}))), listvalues = List.Transform(listsums, each concatvalues{_+1}), result = input & Text.Combine(listvalues, "") in resultPat
Hi ppm1 I modified your code to give me that lower case O in the middle, and it goes like this:
let input = [columnname], letters = {"A".."Z"}, concatvalues = {null, "A".."Z", "0".."5"}, splitinput = List.Split(Text.ToList(Text.Upper(input)), 5), lettercheck = List.Transform(splitinput, (x)=> List.Transform(x, (y)=> Number.From(List.Contains(letters, y)))), multipliers = {1, 2, 4, 8, 16}, listsums = List.Transform(lettercheck, (a)=> List.Sum(List.Transform({0..4}, (b)=> a{b} * multipliers{b}))), listvalues = List.Transform(listsums, each concatvalues{_+1}), result = input & Text.Combine(listvalues, "") in resultThanks for the help and hope I can come back to you with feedback and more help
Hi ppm I modified your code to give me that lower case O in the middle, and it goes like this:
let
input = [Opportunity ID],
letters = {"A".."Z"},
concatvalues = {null, "A".."Z", "0".."5"},
splitinput = List.Split(Text.ToList(Text.Upper(input)), 5),
lettercheck = List.Transform(splitinput, (x)=> List.Transform(x, (y)=> Number.From(List.Contains(letters, y)))),
multipliers = {1, 2, 4, 8, 16},
listsums = List.Transform(lettercheck, (a)=> List.Sum(List.Transform({0..4}, (b)=> a{b} * multipliers{b}))),
listvalues = List.Transform(listsums, each concatvalues{_+1}),
result = input & Text.Combine(listvalues, "")
in
result
Thanks for the help and hope I can come back to you with feedback and more help
