Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Last active February 22, 2022 21:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ImkeF/4a8dd74a2d328b00939bd63945d5cbac to your computer and use it in GitHub Desktop.
Save ImkeF/4a8dd74a2d328b00939bd63945d5cbac to your computer and use it in GitHub Desktop.
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