Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
2 years ago
Solved

Custom Function to multiply Columns

Dear all,   based on the very good help I got in this forum, I tried to build the followin custom function:   (Input_Table as table, Introduction_Rate as text, Step_Split as text) => let // a ...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi Mic1979, you are making things complicated - this is not the first time. I've helped you also last time - but you ignored my solution. I give you one more try 😉

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1DfJ0dgxW0lEyNAASpgZgFog00DEEk6ZKsTrUUmgEJs2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ragion = _t, TOTAL_A = _t, SOME_VALUE = _t, TOTAL_B = _t, #"% INTRODUCTION COEFFICIENT" = _t, #"%" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"TOTAL_A", type number}, {"SOME_VALUE", type number}, {"TOTAL_B", type number}, {"% INTRODUCTION COEFFICIENT", type number}, {"%", type number}}),
    RenamedColumnsDynamic = Table.TransformColumnNames(ChangedType, each Text.Replace(_, "TOTAL", "SMOOTH")),
    MultipliedSmoothColumns = Table.ReplaceValue(RenamedColumnsDynamic,
        each [#"% INTRODUCTION COEFFICIENT"] * [#"%"],
        null,
        (x,y,z)=> x * y,
        List.Select(Table.ColumnNames(RenamedColumnsDynamic), (x)=> Text.StartsWith(x, "SMOOTH")) ),
    RestoredTypes = Value.ReplaceType(MultipliedSmoothColumns, Value.Type(RenamedColumnsDynamic))
in
    RestoredTypes

     

PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

Hi Mic1979 ,

I think any solution with replacevalues or transform columns is not going to work:

  • Replacevalues needs a value to change, separate for each row. But because you don't have the names of the columns at the time of writing the query, this is very hard or not even possible.
  • TransFormColumns does not give you access to the current record.

So I wrote a custom function. And I tested it:

  • Table1 as start

  • The custom function transform_table applied to columns C1 and C12

 

 

let
    Source = Table1,
    transform_table =    (
              T as table              //  table to transform
            , F as function           /*  function called for each record in the table, for each column to be transformed) 
                                            taking 3 arguments: 
                                                the currect record
                                                the name of the column it is being called for
                                                the value of the column it is called for
                                        */
            , optional Cols as list   //  list of column names to transform. Defaults to all columns
        ) =>
            let
                Cols = // Replace by Defaults
                    let 
                        all_cols = Table.ColumnNames(T),    // use if no valid columns are given
                        Cols = List.Intersect({Cols ?? {}, all_cols})   // get the valid columns given
                    in 
                        if List.Count(Cols) > 0 then Cols else all_cols,    // no vlid columns given? Use all columns

                // function transforming the current row
                transform_current_row_function = 
                    (CurrentRow) =>             
                        List.Accumulate(            // loop to all columnnames given, calling Record.TransformFields with F as 
                              Cols
                            , CurrentRow
                            , (current_record,current_column_name) => Record.TransformFields(current_record,{ current_column_name, (current_column_value) => F(current_record, current_column_name, current_column_value)})
                            ), 
                // call the row transform function for each row, prodcing a list of records
                result_as_list = Table.TransformRows(T,transform_current_row_function),
                // convert the list of records to a Table
                result_as_table = Table.FromRecords(result_as_list)
            in
                result_as_table
        ,
    transform_function = (_, ColumnName, Column_Value) => Record.Field(_, ColumnName) * [Introduction_Rate] * [Step_Split]
in
    transform_table(Source, transform_function, {"C1", "C12"})

 

 

  • The result:
  • In your query:
    • add the custom function to your query
    • and replace

 

calcTable = Table.ReplaceValue (
Input_Table,
null,
null,
(val, old, new) => val * Introduction_Rate * Step_Split,
oldColumnNames
),​

by

transform_function = (_, ColumnName, Column_Value) => Record.Field(_, ColumnName) * [Introduction_Rate] * [Step_Split]

calcTable = transform_table(Input_Table, transform_function, oldColumnNames)​,