Dynamically apply column transformations

[ChemEnger]

I have a 'data' table where I'd like to dynamically change the number of decimal places.

I use a 'dictionary' table that holds the names of the columns in 'data' along with the desired number of decimal places, e.g.

ColumnName	Decimal
Input	2
Temperature	1
Pressure	0
Weight	3

I would like to be able to perform a TransformColumns operation on 'data', such as

Table.TransformColumns(data, {{"Temperature", each Number.Round(_, 1), type number}})

But driven dynamically from the 'dictionary' table

I am thinking that looping through the number of decimal places might be a start and can easily generate a list like:

  MinDecimal = List.Min(dictionary[Decimal]),
  MaxDecimal = List.Max(dictionary[Decimal]),
  Range = List.Generate(() => MinDecimal, each _ <= MaxDecimal, each _ + 1)

It feels like I could then use a recursive function (with or without the Range list) to select one by one the columns that need each transform, something like the answer from @ImkeF  in Table Transformations for Dynamic Columns :

fnTransform = (InputTable as table, ListOfColNames, TransformFunction as function) =>
let
    TransformFunctionList = List.Transform(ListOfColNames, (x) => {x, TransformFunction}),
    Transform = Table.TransformColumns(InputTable, TransformFunctionList)
in
    Transform

// Somehow loop n = from MinDecimal to MaxDecimal
ColumnsToTransform = Table.SelectRows(dictionary, each [Decimal] = n)[ColumnName],
Transform = fnTransform(data, ColumnsToTransform, each Number.Round(_, n), type number)

but I am stumped as to the way to use a loop to make this work...

[ImkeF · Accepted solution]

Hi @ChemEnger ,
it could work like so:

(InputTable as table, dictionary as table) =>
  let
    TransformFunctionList = List.Transform(
      Table.ToRecords(dictionary), 
      (record) => {record[ColumnName], each Number.Round(_, record[Decimal]), type number}
    ), 
    Transform = Table.TransformColumns(InputTable, TransformFunctionList)
  in
    Transform

 No real recursion needed. Just transform your dictionary table into a list of records and create the transformation list elements out of it.

[ChemEnger]

Spot on, thank you @ImkeF.

I was looking at something similar on Medium - Transform Multiple Columns Based On Other Columns In Power Query but your solution worked first time 🙂