Substitute in Power BI

[shansakhi]

Hello,
How can get below output in Power BI.
If blank then WS, rest based on input data

interline working_12Mar23 (version 1).xlsb
	U	V
12	Input	Output
13	AB8195~CD9597	AB CD
14	~**5362	**
15	~AB0875	AB
16	BF0816~	BF
17	**5774~**5362	** **
18	**5774~HM0135	** HM
19		WS
20	HM0128~**5027	HM **
Sheet2

Regards,

 

[JEC]

I would do this in the power query editor
Change the source line to your source

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    out = Table.TransformColumns(Source,{{"Input", each Text.Trim(Text.Combine(List.Transform(try Text.Split(_, "~") otherwise {"WS"}, (x)=> Text.Start(x,2)), " ")) , type text}})

in
   out

 

[shansakhi]

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], out = Table.TransformColumns(Source,{{"Input", each Text.Trim(Text.Combine(List.Transform(try Text.Split(_, "~") otherwise {"WS"}, (x)=> Text.Start(x,2)), " ")) , type text}}) in out

Thank you. I tried this but it is replacing the original Column. I want to keep the original column as it is. The output should be as new column

 

[JEC]

Alright, then

out = Table.AddColumn(Source, "Output", each Text.Trim(Text.Combine(List.Transform(try Text.Split([Input], "~") otherwise {"WS"}, (x)=> Text.Start(x,2)), " ")))

 

[GraH]

Ad a conditional column. Follow the UI guide to complete the if then else statement.

 

[GraH]

If the above syntax is a bit obscure, then you can add a conditional column. Follow the UI guide to complete the if then else statement as the besics. Then update it
If [Input] = "" then "WS" else Text.Select( [Input], {"A" .. "Z" , "*", "~"})
Finally replace tilde by space.

 

[shansakhi]

Alright, then

out = Table.AddColumn(Source, "Output", each Text.Trim(Text.Combine(List.Transform(try Text.Split([Input], "~") otherwise {"WS"}, (x)=> Text.Start(x,2)), " ")))

Thank you. Its working as desired

 

[shansakhi]

If the above syntax is a bit obscure, then you can add a conditional column. Follow the UI guide to complete the if then else statement as the besics. Then update it
If [Input] = "" then "WS" else Text.Select( [Input], {"A" .. "Z" , "*", "~"})
Finally replace tilde by space.

Thank you. May I request you to please assist about UI guide.

 

[shansakhi]

If the above syntax is a bit obscure, then you can add a conditional column. Follow the UI guide to complete the if then else statement as the besics. Then update it
If [Input] = "" then "WS" else Text.Select( [Input], {"A" .. "Z" , "*", "~"})
Finally replace tilde by space.

Yes.. This is also working as desired. Thank you.

 

[GraH]

If meant you can follow the helper screen when you click on add conditional column.
But seels you figured it out.