Forum Discussion

ChemEnger's avatar
ChemEnger
Advocate V
2 years ago
Solved

Dynamically apply column transformations

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.

ColumnNameDecimal
Input2
Temperature1
Pressure0
Weight3

 

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

Need Help
  • ImkeF's avatar
    ImkeF
    2 years ago

    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.

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    2 years ago

    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.