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
    2 years 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

     

tharunkumarRTK's avatar
tharunkumarRTK
Super User
2 years ago

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's avatar
Mic1979
Post Partisan
2 years ago

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.