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...
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:
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:
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.
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.
