New column based off values from two existing columns

[FLYKOBE24]

Hello, 

I am trying to replicate this SQL query in Power BI power query. 

 Replace([WBS],"XXXX",[Task Name]) AS WBSTask, Timesheet_Entries_T.WBS,

Basically i want a new column that takes the values from my column WBS and replaces the XXXX with the values from my Task Name Column

RESULT:

WBS: 1.21.01A.XXXX-700 Task Name: PM40 New Column: 1.21.01A.PM40-700 

[ronrsnfld · Accepted solution]

Paste the code below into the Advanced Editor. The magic happens at the #"Add New Column" step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMtQzMHTUqwACXXMDAyUdpQBfEwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WBS = _t, #"Task Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WBS", type text}, {"Task Name", type text}}),
    #"Add New Column" = Table.AddColumn(#"Changed Type","New Column", 
        each Text.BeforeDelimiter([WBS],"xxxx") & [Task Name] & Text.AfterDelimiter([WBS],"xxxx"), type text)
in
    #"Add New Column"

See also the Add Custom Column:

[FLYKOBE24]

Thank you very much @ronrsnfld worked great!!!!

Wow alot more to it than i thought the SQL syntax is much easier.

I appreciate your help. 

[ronrsnfld]

You can use syntax similar to SQL:

Text.Replace([WBS],"xxxx","Task Name")

However, using the diagnostic tools available in Power BI, that syntax runs considerably slower than the more complicated version I presented.