Replacing a value based on 2 other columns

[karmaimages]

Hi

I have a column where I need to change 1 value within the column based on 2 other columns,

Rule	Regulator
Communication	ICO

There are lots of other values in the rule column, but I want to change the value where

[Rule] = "Communication" & [Regulator] = "ICO" then new value = "Data Protection"

I can't seem to work out how to perform a multi value replace value if anyone can help?

 

[smozgur]

If you are adding a new column based on two column values:

NewColumn = Table.AddColumn(Source, "NewColumn", each if [Rule] = "Communication" and [Regulator] = "ICO" then "Data Protection" else "")

If you are trying to "edit" an existing column value then it is a bit different. You can either add the new column as shown above and set the "else" value as the existing column row value, then remove the existing column and use the new column as the removed column.

Or, you can edit the existing column's row values by using some M code. Sample code below:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PzS3Ny0xOLMnMz1PSUfJ09geSYYk5pamGSrE60UrB+bmpJRmZeekKqTnFqUA5Z39PmAojsAqCRqArcPZEUxCaV5Sak1iSmoKqG7vx/s5w+42VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rule = _t, Regulator = _t, ExistingColumn = _t]),
  
  ReplaceExisting = Table.FromRecords(Table.TransformRows(Source, 
                        (r) => Record.TransformFields(r, {"ExistingColumn", 
                                        each if r[Rule] = "Communication" and r[Regulator] = "ICO" then "Data Protection" else _ })))
in
  ReplaceExisting

The following code shows each mentioned method separately. Click on the steps to see individual results that transforms the Source by using each method:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PzS3Ny0xOLMnMz1PSUfJ09geSYYk5pamGSrE60UrB+bmpJRmZeekKqTnFqUA5Z39PmAojsAqCRqArcPZEUxCaV5Sak1iSmoKqG7vx/s5w+42VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rule = _t, Regulator = _t, ExistingColumn = _t]),
  
  NewColumn = Table.AddColumn(Source, "NewColumn", each if [Rule] = "Communication" and [Regulator] = "ICO" then "Data Protection" else ""),
  
  ReplacementColumn = Table.RemoveColumns(Table.AddColumn(Source, "ReplacementColumn", each if [Rule] = "Communication" and [Regulator] = "ICO" then "Data Protection" else [ExistingColumn]), {"ExistingColumn"}),
  
  ReplaceExisting = Table.FromRecords(Table.TransformRows(Source, 
                        (r) => Record.TransformFields(r, {"ExistingColumn", 
                                        each if r[Rule] = "Communication" and r[Regulator] = "ICO" then "Data Protection" else _ })))
in
  ReplaceExisting