Substitute in Power BI

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hello,
How can get below output in Power BI.
If blank then WS, rest based on input data

interline working_12Mar23 (version 1).xlsb
UV
12InputOutput
13AB8195~CD9597AB CD
14~**5362**
15~AB0875AB
16BF0816~BF
17**5774~**5362** **
18**5774~HM0135** HM
19WS
20HM0128~**5027HM **
Sheet2



Regards,
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I would do this in the power query editor
Change the source line to your source

Power Query:
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
 
Upvote 0
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
 
Upvote 0
Alright, then

Power Query:
out = Table.AddColumn(Source, "Output", each Text.Trim(Text.Combine(List.Transform(try Text.Split([Input], "~") otherwise {"WS"}, (x)=> Text.Start(x,2)), " ")))
 
Upvote 0
Solution
Ad a conditional column. Follow the UI guide to complete the if then else statement.
 
Upvote 0
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.
 
Upvote 0
Alright, then

Power Query:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If meant you can follow the helper screen when you click on add conditional column.
But seels you figured it out.
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,573
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top