Forum Discussion
Solved
New column based off values from two existing columns
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
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:
3 Replies
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:
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.
