Search code examples
excelpowerquerydata-cleaningm

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.

enter image description here

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

enter image description here

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"
Solution

  • 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"