none
How to multiply or divide multiple coloumn using m code at single query RRS feed

  • Question

  • we want to multiply multiple columns using another columns value in power query but this is taking only single columns at a time. Can you please help us out using a m Query code on this.

    Thursday, July 18, 2019 10:22 AM

Answers

  • Hi, 

    If you want to use different calculation for each column, you may add desired transformations to Table.TransformColumns function:

    let
        Source = #table(3, List.Zip({{1..3},{1..3},{1..3}})),
        mult = Table.TransformColumns(Source, {{"Column1", each _ +1}, {"Column2", each _ *10}, {"Column3", each _ / 2}})
    in
        mult

    If you want to make same calculation for all columns based on another column, you may use following approach:

    let
        Source = #table(4, List.Zip({List.Numbers(100,5,100),{1..5},{6..10}, {11..15}})),
        replace = Table.ReplaceValue(Source,0,each [Column1],(a,b,c)=>a*c,{"Column2", "Column3", "Column4"})
    in
        replace


    Thursday, July 18, 2019 12:02 PM

All replies

  • Hi, 

    If you want to use different calculation for each column, you may add desired transformations to Table.TransformColumns function:

    let
        Source = #table(3, List.Zip({{1..3},{1..3},{1..3}})),
        mult = Table.TransformColumns(Source, {{"Column1", each _ +1}, {"Column2", each _ *10}, {"Column3", each _ / 2}})
    in
        mult

    If you want to make same calculation for all columns based on another column, you may use following approach:

    let
        Source = #table(4, List.Zip({List.Numbers(100,5,100),{1..5},{6..10}, {11..15}})),
        replace = Table.ReplaceValue(Source,0,each [Column1],(a,b,c)=>a*c,{"Column2", "Column3", "Column4"})
    in
        replace


    Thursday, July 18, 2019 12:02 PM
  • best to do it through Records:
    let's multiply columns "col2" "col3" and "col4" by values in column "col1":

    let
        Source = #table(
                    type table [col1=number, col2=number, col3=number, col4=number],
                    List.Zip({{1..3},{10..12},{20..22},{30..32}})
        ),
        col_to_transform = {"col2","col3","col4"},                            //list of the names of the columns to transform
        Replace = Table.FromRecords(Table.TransformRows(#"Source", (row) => 
            let
                pct = row[col1],
                Transforms = List.Transform(col_to_transform, (col_name) => { col_name, (cell) => cell * pct})
            in Record.TransformFields(row, Transforms))),
        ReplaceType = Value.ReplaceType(#"Replace", Value.Type(#"Source"))    //optional, used to transfer the types
    in #"ReplaceType"



    • Edited by anthony34 Thursday, July 18, 2019 1:33 PM
    Thursday, July 18, 2019 1:28 PM
  • Hi, 

    If you want to use different calculation for each column, you may add desired transformations to Table.TransformColumns function:

    let
        Source = #table(3, List.Zip({{1..3},{1..3},{1..3}})),
        mult = Table.TransformColumns(Source, {{"Column1", each _ +1}, {"Column2", each _ *10}, {"Column3", each _ / 2}})
    in
        mult

    If you want to make same calculation for all columns based on another column, you may use following approach:

    let
        Source = #table(4, List.Zip({List.Numbers(100,5,100),{1..5},{6..10}, {11..15}})),
        replace = Table.ReplaceValue(Source,0,each [Column1],(a,b,c)=>a*c,{"Column2", "Column3", "Column4"})
    in
        replace


    Hi Aleksei,

    Can you explain how you came up with that structure for Table.ReplaceValue? I understand what the replacer function ((a, b, c) => a * c) is doing, but I've never seen that form of function in Table.ReplaceValue before.

    Thursday, July 18, 2019 6:43 PM
  • Hi Colin,

    First I've seen syntax (a,b,c)=>null instead of Replacer.ReplaceValue in post on Russian-language forum. Then I've found out, what a, b, c mean in this formula:

    In most scenarios b is not needed and may be equals any value (for brevity, I usually use 0).

    Lately I use only this syntax of Table.ReplaceValue, on my opinion it is more handy and neat, than default syntax.

    Friday, July 19, 2019 12:52 PM
  • Hi Colin,

    First I've seen syntax (a,b,c)=>null instead of Replacer.ReplaceValue in post on Russian-language forum. Then I've found out, what a, b, c mean in this formula:

    In most scenarios b is not needed and may be equals any value (for brevity, I usually use 0).

    Lately I use only this syntax of Table.ReplaceValue, on my opinion it is more handy and neat, than default syntax.

    Hi Aleksei,

    Thanks for the response. I wonder how the Russian folks discovered this. Must have hacked into the Power Query development servers. :) For a single column replacement, the standard syntax is more readable.

    Like you, I figured out what the parameters refer to. For self documentation, and to better identify what each parameter refers to, I've using the names (searchCols, oldVal, newVal), which are abbreviated from the function documentation parameter names.  

    Friday, July 19, 2019 5:19 PM
  • As an amendment to Aleksei's post:

    One issue with this is that the column type goes to type any. However if you apply a return type to the transformation function then that new column type will be one that conforms.

    Here is some example code to try out:

    let
        //Note that Column2 is of type Int64.Type
        Source = Table.FromColumns({List.Numbers(100,5,100),Value.ReplaceType({1..5}, type {Int64.Type}),{6..10}, {11..15}}),
    
        //This is the original function and return type of the (a,b,c) function is type any
        #"Col2 goes to type any" = Table.ReplaceValue(Source,0,each [Column1],(a,b,c)=>a*c,{"Column2", "Column3", "Column4"}),
        
         // A simple but limited way of applying function return types
        #"Col2 goes to type number"= Table.ReplaceValue(Source,0,each [Column1],(a,b,c) as number=>a*c,{"Column2", "Column3", "Column4"}),
        
        // You can also apply subtypes and complex types
        #"Col2 goes to Int64.Type"= Table.ReplaceValue(Source,0,each [Column1],Value.ReplaceType((a,b,c)=>a*c,Type.ForFunction([ReturnType = Int64.Type, Parameters = [a = type number, b = type number, c = type number]],3)),{"Column2", "Column3", "Column4"}), 
        
        //type text isn't compatible with Int64.Type so type any
        #"Col2 stays as type any again" = Table.ReplaceValue(Source,0,each [Column1],(a,b,c) as text=>a*c,{"Column2", "Column3", "Column4"}) 
    in
        #"Col2 stays as type any again"
    Edit: It's worth noting that applying Percentage.Type with Int64.Type gives you the "parent" type: type number as this is the type that compatible with both. (Gone a little off topic here, but I think it's worth noting how Table.ReplaceValue works).   

    • Edited by CamWally Saturday, July 20, 2019 5:15 AM
    Saturday, July 20, 2019 2:22 AM
  • Hello Ravzhero,

    did any of the solutions provided solve your problem? 

    If so, please mark it as answer.

    If not, please provide details on what's missing.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, August 4, 2019 9:28 PM
    Moderator

  • In most scenarios b is not needed and may be equals any value (for brevity, I usually use 0)

    Aleksei,

    could you please elaborate about parameter b.
    how do you use it, and when ?

    Monday, August 12, 2019 11:53 AM
  • Hi Anthony!

    There is no difference between b and c parameters. You may split condition of replacement into two parameters, whenever you want. Taking example from this thread, following formulas are equivalent:

    replace = Table.ReplaceValue(YourTable,
                    each Text.Contains([Location], "York"),
                    "Tim",
                    (a,b,c) => if b then c else a,
                    {"Manager Name"})
    replace = Table.ReplaceValue(YourTable,
                    0,
                    each if Text.Contains([Location], "York") then "Tim" else [Manager Name],
                    (a,b,c) => c,
                    {"Manager Name"})
    replace = Table.ReplaceValue(YourTable,
                    each if Text.Contains([Location], "York") then "Tim" else [Manager Name],
                    0,
                    (a,b,c) => b,
                    {"Manager Name"})
    Monday, August 12, 2019 1:16 PM
  • Perfect, it simplifies some code so much, Thank you
    Monday, August 12, 2019 2:16 PM