none
How to add/sum N columns without having to add them one by one (maybe use a loop?) RRS feed

  • Question

  • I have a table with N columns (which is variable). I want to create N more columns based on each of these. Is this possible in Power Query without having to do each on individually which means updating the query each time the number of columns changes?

    Is there a way of looping columns in Power Query?

    Related to this: Is there a way of adding one column which is the sum of all N columns? With N being variable?

    Cheers!

    • Edited by AdamWLavan Friday, August 28, 2015 10:53 AM
    Friday, August 28, 2015 10:50 AM

Answers

  • Hi Adam,

    You can add all columns using this construction

    YourStep = Table.AddColumn(PreviousStep, "SumOfAllColumns", each List.Sum(Record.ToList(_)))
    Regards

    • Marked as answer by AdamWLavan Friday, August 28, 2015 12:53 PM
    Friday, August 28, 2015 12:44 PM

All replies

  • Hi Adam,

    You can add all columns using this construction

    YourStep = Table.AddColumn(PreviousStep, "SumOfAllColumns", each List.Sum(Record.ToList(_)))
    Regards

    • Marked as answer by AdamWLavan Friday, August 28, 2015 12:53 PM
    Friday, August 28, 2015 12:44 PM
  • You unpivot your N columns (http://blogs.technet.com/b/gilraviv/archive/2015/08/27/unpivot-data-with-excel.aspx)  and add one new column containing the new values.

    Then you pivot back on these 2 values using this technique: http://www.thebiccountant.com/2015/08/12/how-to-pivot-on-multiple-measurescolumns-in-power-query/

    & voila: N additional columns, dynamic

    This step will deliver the Sum of all N columns in unpivot-state:

    Sum = Table.Group(NameOfThePreviousStep, {}, {{"Sum", each List.Sum([Amount]), type number}})

    After that you pivot back (referencing the NameOfThePreviousStep, using this technique: http://www.excelguru.ca/blog/2015/07/29/refer-to-other-steps/

    Last but not least you create your additional column that shows your Sum: 

    = Table.AddColumn(lastStep, "Sum", each StepSum[Sum]{0})

    Whereas StepSum is the Name of the step where you've performed the Group-Operation. [Sum] is the Name of the column and {0} is the row number (as there is only 1 row returned & Power Query starts counting at 0).

    .. Hey Bill, you were quick on this - just reconned after posting :-)

    So my solution gives the opportunity to create a different value into the additional columns instead of just copying them. If this is needed....


    Imke Feldmann TheBIccountant.com


    Friday, August 28, 2015 1:01 PM
    Moderator
  • I am new to PQ. I also see that I am responding very late to this post. This line will add N columns to a table without updating the query each time the number of columns change. The example below multiplies the number by 2. You can modify it to produce your desired result.

    = List.Accumulate(Table.ColumnNames(CT), CT , (state,current) => Table.AddColumn(state, current & ".1", each Record.Field(_,current) * 2, Number.Type))

    Hope this helps!

    Wednesday, March 28, 2018 11:31 AM