Forum Discussion
Excel formula to power query M code
Hi all, Im trying to convert the following formula in excel to M code : =CONCATENATE(H2, MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",( IFERROR(IF(FIND(MID(H2,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,...
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
result
Pat
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
Can I ask, was there any specific reason you used Text.Upper for the input?
