need and assistance, please.
Need to create new column.In column Company i have few values like D 00, DEXI, DX00, DX. I need to make a statement: Everytime when second char in cell is “X” or ” ” should return me DX, else should return me two first chars like DE(second example). Same situation whith other letters, when I have WX, W 00 and so on.
2
You can also try this
=if Text.Middle([Input],1,1)=" " then Text.Start([Input],1) & "X" else Text.Start([Input],2)
1
Create a table, here Table1, that shows which character you want to look up as the second character and what you want to return if it is found
On another table, bring in your data, and add column .. custom column …
let a=Text.Range([Column1],1,1), b= List.PositionOf(Table1[SecondCharacter],a), c= if b<0 then Text.Start([Column1],2) else Table1[Show]{b} in c
being sure to replace Column1 with the name of the column you are actually looking up in the input table
0
Paste this code into the Advanced Editor.
Change the table name in the Source line as needed.
let
//change next line to reflect actual table name
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
#"First Two" = Table.AddColumn(#"Changed Type","Output",(r)=>
let
split = Text.ToList(r[Input]),
secondCharacter= if split{1}=" " then "X" else split{1}
in split{0} & secondCharacter, type text)
in
#"First Two"