Table.ReplaceValue Function to Replace Multiple Columns

Table.ReplaceValue function allows you to replace values across multiple columns without needing iterator functions like List.Transform or List.Accumulate.

The ColumnsToSearch parameter in the Table.ReplaceValue function lets you specify which columns to replace.

In this post, I will demonstrate how to perform custom replacements across several columns.

The source data includes monthly sales broken down by region.

Our goal is to compute the monthly percentage contribution of sales for each region.

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Cols = List.Skip( Table.ColumnNames(Source)),

 Replace = Table.ReplaceValue(Source, each List.Sum(List.Skip(Record.ToList(_))), null, (CurrVal,OldVal,NewVal) => CurrVal/OldVal , Cols),

 Result = Table.TransformColumnTypes(Replace, List.Transform(Cols, (f) => {f, Percentage.Type }))

in

Result