Trick to Update a Record's Field Value - Power Query

In this post, we will discuss a technique for modifying the value of an existing field within a record.

Let's say our record holds the values of

[a = 2, b = 3, c = 4]

To change the value of the field 'a', we can combine the current record with a new record as follows:

[a = 2, b = 3, c = 4] & [a = 5]

This will adjust the field value of 'a' to 5, resulting in:

[a = 5, b = 3, c = 4]

I will demonstrate this approach with a practical example.

Our source data includes columns for EmployeeID, Name, Hiring Date and ManagerID.

Due to shuffle at the Manager level, we have a new table containing EmployeeID and the updated ManagerID.

Our objective is to reflect the new ManagerID in the source data.

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.FromRecords(List.Transform(Table.TransformRows(Source("data"), each _), (f) => f & [ ManagerID = Source("NewID"){[EmployeeID = f[EmployeeID]]}[ManagerID] ]))

in

 Result

data refers to Employee Table,

NewID refers to New MangerID table.