Excel Power Query: How to add a number to all the columns in one step

I am trying to add a number or numbers to all the rows in multiple columns. For now, i have to hold down each of the columns from transform tab standard add and entering the numbers one by one. Thus, increasing the number of steps.

Sometimes I have 30 columns so should i add 30 steps to my query, and numbers may be same sometimes and may not be. What is the right way of doing it. It takes me lot of time while doing this and i am not able to figure out. How can i do it in one step is it possible to do?

Another issue is when I hold down all the columns and try to add the number the standard feature greys out. Do you all think i am doing wrong, can anyone suggest the right way of doing it

I tried.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Full Name", type text}, {"1979", Int64.Type}, {"1990", Int64.Type}, {"1997", Int64.Type}, {"2007", Int64.Type}, {"2010", Int64.Type}}),
    #"Added to Column" = Table.TransformColumns(#"Changed Type", {{"1979", each _ + 5, type number}}),
    #"Added to Column1" = Table.TransformColumns(#"Added to Column", {{"1990", each _ + 10, type number}}),
    #"Added to Column2" = Table.TransformColumns(#"Added to Column1", {{"1997", each _ + 20, type number}}),
    #"Added to Column3" = Table.TransformColumns(#"Added to Column2", {{"2007", each _ + 15, type number}}),
    #"Added to Column4" = Table.TransformColumns(#"Added to Column3", {{"2010", each _ + 30, type number}})
in
    #"Added to Column4"

Here's another approach where you need to know

• which columns need the additions
  • I assumed it as all except the first
• How much will be added to each column

The three lists I created could all be included in the #"Add to Columns" statement but I split them out for clarity.

let
    Source = Excel.CurrentWorkbook(){[Name="your_table_name"]}[Content],

//Creat List of all column names except the first
    colNames = List.RemoveFirstN(Table.ColumnNames(Source),1),

//Create List of the additions as a function
    Adds = List.Transform({5,10,15,20,25}, (n)=> each _ + n),

//Create LIst of the data types
    Types = List.Repeat({Int64.Type}, List.Count(colNames)),

//Create transfrom statement
    #"Add to Columns" = Table.TransformColumns(Source,List.Zip({colNames, Adds, Types}))

in
    #"Add to Columns"

To do this in one step, you'll have to type in the formula bar. Click the first column and add a number to it to get the code generated for you and you'll have this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Full Name", type text}, {"1979", Int64.Type}, {"1990", Int64.Type}, {"1997", Int64.Type}, {"2007", Int64.Type}, {"2010", Int64.Type}}),
    #"Added to Column" = Table.TransformColumns(#"Changed Type", {{"1979", each _ + 5, type number}})
in
    #"Added to Column"

Your last step in the formula bar will look like this:

The last parameter is a list of lists and you can just copy and paste in as many as you want. e.g.

= Table.TransformColumns(#"Changed Type", {{"1979", each _ + 5, type number}, {"1990", each _ + 10, type number}, {"2007", each _ + 15, type number}})