Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Transforms multiple columns (defined in <code>FieldList</code>) of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. Optional parameters allow to pass addional function arguments and define a new type.
let func =
// fnTransformMultipleColumns
let
fnTransformMultipleColumns = (
SourceTable as table,
FieldList,
TransformationFunction,
optional FunctionArgumentsList,
optional newType
) =>
let
FunctionArguments = if FunctionArgumentsList = null then {} else FunctionArgumentsList,
Source = Table.AddColumn(
SourceTable,
"**tempColumn**",
each Record.FromList(
List.Transform(
FieldList,
(l) =>
Function.Invoke(
TransformationFunction,
{Record.Field(_, l)} & {_} & FunctionArguments
)
),
FieldList
)
),
RemoveOldColumns = Table.RemoveColumns(Source, FieldList),
ExpandNewColumns = Table.ExpandRecordColumn(
RemoveOldColumns,
"**tempColumn**",
FieldList
),
RestoreType = Value.ReplaceType(
Table.ReorderColumns(ExpandNewColumns, Table.ColumnNames(SourceTable)),
Value.Type(SourceTable)
),
ApplyNewTypes = Table.TransformColumnTypes(
ExpandNewColumns,
List.Transform(FieldList, each {_, newType})
),
Result =
if Value.Type(newType) = type type then
ApplyNewTypes
else if newType = null or newType = true then
RestoreType
else
ExpandNewColumns
in
Result
in
fnTransformMultipleColumns ,
documentation = [
Documentation.Name = " Table_TransformMultipleColumns ",
Documentation.Description = " Transforms multiple columns of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. ",
Documentation.LongDescription = " Transforms multiple columns (defined in <code>FieldList</code>) of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. Optional parameters allow to pass addional function arguments and define a new type. ",
Documentation.Category = " Table ",
Documentation.Source = " www.TheBIcountant.com ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " let
SourceTable = #table( type table [Product = Text.Type, Rate = Number.Type, Sales = Int64.Type, CoS = Int64.Type],
// Product| Rate| Sales| CoS|
{//--------|-----|------|----|
{ ""A"", 1.2, 100, 40 },
{ ""B"", 0.9, 200, 70 } } ),
FieldList = {""Sales"", ""CoS""},
TransformationFunction = (currentItem, _) => currentItem * _[Rate],
FunctionCall = Table_TransformMultipleColumns(SourceTable, FieldList, TransformationFunction)
in
FunctionCall ",
Result = " #table( type table [Product = Text.Type, Rate = Number.Type, Sales = Int64.Type, CoS = Int64.Type],
// Product| Rate| Sales| CoS|
{//--------|-----|------|----|
{ ""A"", 1.2, 120, 48 },
{ ""B"", 0.9, 180, 63 } } )
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
@hohlick
Copy link

hohlick commented Feb 21, 2022

Hi Imke,
While it is excellent code at all, the TransformationFunction definition looks too confusing. Why not define TransformationFunction as a function of 3 arguments:

  1. Value: value as any to be transformed,
  2. A table row: row as record
  3. Additional arguments as list: optional args as nullable list

Then any TransformationFunction passed to Table_TransformMultipleColumns shall always be the 3-arguments function (anyway, we need to write this function manually, so we always could refer to them as arg{0}, arg{1} etc).
Or, maybe additional arguments could be a record. Then it is much easier to refer to them in the custom TransformationFunction.

What do you think?

@ImkeF
Copy link
Author

ImkeF commented Feb 21, 2022

Hi Maxim,
thanks for the feedback, but I don't understand, as my transformation function requires just those 3 elements as you have described.
Where is the difference? Maybe you could provide a sample?
Thanks a lot!

@hohlick
Copy link

hohlick commented Feb 22, 2022

@ImkeF sorry, I was unclear.
First, I meant that it should be directly mentioned that:

  • TransformationFunction has to have two mandatory arguments: value to be changed and row as record
  • It could accept additional arguments, which should be provided as list in FunctionArgumentsList

And my suggestion was that, for example, in your case I need to define TransformationFunction = (value, row, a, b, c, d, e)=>, and when invoked by Table_TransformMultipleColumns it will look like this:
Table_TransformMultipleColumns(SourceTable, FieldList, TransformationFunction, {a,b,c,d,e})

For some reasons I thought that if TransformationFunction will be defined as the function of exactly 3 arguments (value as any, row as record, optional arguments as list), it made its definition more strict and clear. But it isn't )))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment