Custom Function to multiply Columns

[Mic1979]

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 list of all column names that contain the "TOTAL"
oldColumnNames = List.Select(Table.ColumnNames(Input_Table), each Text.Contains(_, "TOTAL")),
// replace "TOTAL" with "SMOOTH"
newColumnNames = List.Transform(oldColumnNames, each Text.Replace(_, "TOTAL", "SMOOTH")),
calcTable = Table.ReplaceValue (
Input_Table,
null,
null,
(val, old, new) => val * Introduction_Rate * Step_Split,
oldColumnNames
),
// rename column name
renameColumns = Table.RenameColumns(calcTable, List.Zip({oldColumnNames, newColumnNames}))
in
renameColumns

The target for me is:

1. Select all the columns I have in my table containing the word "TOTAL"
2. Replace the word "TOTAL" to "SMOOTH"
3. In the table with columns named "TOTAL", multiply those columns with other two columns of the same table, named "Introduction_Rate" and "Step_Split"

Name replacement from "TOTAL" to "SMOOTH" works, while the multiplication doesn't work. I get no error message, but in the table I get "Error" in those columns.

Thanks for your support in advance.

[PwerQueryKees]

It was a nice challenge by the way and addressing a problem I had myself earlier but never took the time to solve properly. I now have one more function to add to my own personal libraray of reusable functions 😊

[PwerQueryKees]

In your original query and in some of the solutions posted earlier by others, I saw [Introduction_Rate] * [Step_Split] being referred to without the []. This will never work because you refer to a field in a record or column in a table.

Also be aware!

The first parameter to the transform_function is an underscore. This allows you to refer to the field in the record passed into _, without specifying it explicitly: [Introduction_Rate] is equivalent to _[Introduction_Rate].

[PwerQueryKees]

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)​,

[dufoq3 · Accepted solution]

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

[Mic1979]

Many thanks. 

I apologize but I am tring to learn as fast as I can..

[dufoq3]

You're welcome 😉

For future requests provide sample data in usable format so we can copy/paste (if you don't know how, read note below my post). Don't forget to provide also expected result based on sample data (at least screenshot)

[ronrsnfld]

To make it easier to assist you, please provide a representative data sample (with confidential information obfuscated) as text that can be copy/pasted, and also a screenshot (or text table) of your desired results from that data.

[Mic1979]

My target is to have a function to pass from the first table to the second one, multypling all the column with the last two ones ("Introduction_Rate" and "Step_Split").

Here you have the scrrenshot.

.

The second table is has empty columns because I have errors in the function.

Hoping this clarifies.

Many thanks.

[ronrsnfld]

For some reason, you chose to post neither item I requested. And what you did post is not helpful to me. Hopefully someone else will be able to assist you.

[tharunkumarRTK]

@Mic1979 

(Input_Table as table, Introduction_Rate as text, Step_Split as text) =>

let

// a list of all column names that contain the "TOTAL"

oldColumnNames = List.Select(Table.ColumnNames(Input_Table), each Text.Contains(_, "TOTAL")),

// replace "TOTAL" with "SMOOTH"

newColumnNames = List.Transform(oldColumnNames, each Text.Replace(_, "TOTAL", "SMOOTH")),

calcTable = List.Accumulate( oldColumnNames , Input_Table, (s,c)=> Table.TransformColumns(s, {{c, each _ * Introduction_Rate * Step_Split }} ) ),

// rename column name

renameColumns = Table.RenameColumns(calcTable, List.Zip({oldColumnNames, newColumnNames}))

in

renameColumns 

try this code

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Mic1979]

Hello,

Could you clarify the meaning of

calcTable = List.Accumulate( oldColumnNames , Input_Table, (s,c)=> Table.TransformColumns(s, {{c, each _ * Introduction_Rate * Step_Split }} ) )

Thanks.

[tharunkumarRTK]

@Mic1979 

I would suggest you to watch this video for explanation. 
https://www.youtube.com/watch?v=RIo4OrPixco
Also, incase if my answer solves your requirement then pl...

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[PwerQueryKees]

Change 

(val, old, new) => val * Introduction_Rate * Step_Split

to

(val, old, new) => val * [Introduction_Rate] * [Step_Split]

If you still get errors, click on the cell but NOT on the word error and see what shows up at the bottom of your screen...

[Mic1979]

Change done, I got this:

"An error occurred in the ‘’ query. Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"