Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Last active February 22, 2022 21:00
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
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))
@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