Answered by:
How to multiply or divide multiple coloumn using m code at single query
Question

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
 Edited by Aleksei Zhigulin Thursday, July 18, 2019 1:53 PM
 Proposed as answer by Colin Banfield Thursday, July 18, 2019 6:31 PM
 Marked as answer by Imke FeldmannMVP, Moderator Saturday, August 24, 2019 7:19 AM
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
 Edited by Aleksei Zhigulin Thursday, July 18, 2019 1:53 PM
 Proposed as answer by Colin Banfield Thursday, July 18, 2019 6:31 PM
 Marked as answer by Imke FeldmannMVP, Moderator Saturday, August 24, 2019 7:19 AM

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

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.

Hi Colin,
First I've seen syntax (a,b,c)=>null instead of Replacer.ReplaceValue in post on Russianlanguage 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.
 Edited by Aleksei Zhigulin Friday, July 19, 2019 1:52 PM
 Proposed as answer by anthony34 Monday, August 5, 2019 12:35 PM

Hi Colin,
First I've seen syntax (a,b,c)=>null instead of Replacer.ReplaceValue in post on Russianlanguage 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.

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

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! 

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"})
 Edited by Aleksei Zhigulin Monday, August 12, 2019 1:23 PM
