Power Query Consists of three numbers, followed by either X or D,

[mouzzampk]

Hi,

I am having difficult time to list D or X if matches the criteria i.e., format consists of three numbers, followed by either X or D, followed by a further three numbers using power query. If matches the format then in Ouput column add X or D otherwise N.

Book1.xlsx

List	Output
123X325	X
254D365	D
689X555	X
784X444	X
789D748	D
ABCX899	N
3456X879	N
876X8555	N
2466D854	N

Thanks in advance.

[AlienSx · Accepted solution]

@mouzzampk so many ways to do that...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    repl = List.Buffer(List.Transform({"0".."9"}, (x) => {x, null})),
    x = {null, null, null, "X", null, null, null},
    d = {null, null, null, "D", null, null, null},
    fx = (txt) => 
        [lst = List.Buffer(List.ReplaceMatchingItems(Text.ToList(txt), repl)),
        check = if lst = x or lst = d then lst{3} else "N"][check],
    col = Table.AddColumn(Source, "Output", (x) => fx(x[List]))
in
    col

[mouzzampk]

Thank you!!! 😇