none
Calculating sum of all columns without text RRS feed

  • Question

  • I have a query where two columns have text values and other columns have numbers. I would like to sum up only the columns with numbers (excluding the two columns with text). 

    This code gives me an error as it sums all columns (including the ones with text):

    #"Added Column" = Table.AddColumn(#"Replaced Value", "SumOfAllColumns", each List.Sum(Record.ToList(_)))


    How can I modify this to exclude the columns with text values? 

    Thanks a lot!

    Aish


    • Edited by Aish963 Friday, June 30, 2017 12:11 AM
    Friday, June 30, 2017 12:08 AM

Answers

  • Based on your formula, it appears that you are summing the row, and not the column. If this assumption is true, you can modify your step as follows:

    AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum(List.Select(Record.ToList(_), each _ is number)))

    However, unless you have a lot of numeric columns, this formula is overkill, since it requires three function calls.

    If you are not summing values from many columns, I would suggest an alternative that uses just one function call. Assuming that you have three numeric columns named Number1, Number2, and Number3, then

    AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum([Number1], [Number2], [Number3]))


    • Edited by Colin Banfield Friday, June 30, 2017 2:18 AM
    • Marked as answer by Aish963 Friday, June 30, 2017 6:06 PM
    Friday, June 30, 2017 2:14 AM

All replies

  • Based on your formula, it appears that you are summing the row, and not the column. If this assumption is true, you can modify your step as follows:

    AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum(List.Select(Record.ToList(_), each _ is number)))

    However, unless you have a lot of numeric columns, this formula is overkill, since it requires three function calls.

    If you are not summing values from many columns, I would suggest an alternative that uses just one function call. Assuming that you have three numeric columns named Number1, Number2, and Number3, then

    AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum([Number1], [Number2], [Number3]))


    • Edited by Colin Banfield Friday, June 30, 2017 2:18 AM
    • Marked as answer by Aish963 Friday, June 30, 2017 6:06 PM
    Friday, June 30, 2017 2:14 AM
  • Hi Colin, 

    Thanks a lot , it worked! I had to add up values from several columns. So used the first option you mentioned, got the results I wanted.

    Thanks again!

    Aish

    Friday, June 30, 2017 6:08 PM
  • Hi Aish,

    if you have got text in columns whose names are known you can use a slightly different solution ( i did not test performance difference)

    let
        Rec = [C = null, E = null],
        Source = Table.FromRows({{4,2,"Colin",5,"Banfield",15}, {1,7,"Bill",6,"Szysz",12}}, {"A","B","C","D","E","F"}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type number}, {"B", type number}, {"C", type text}, {"D", type number}, {"E", type text}, {"F", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(Record.ToList(_ & Rec)) )
    in
        #"Added Custom"
    Regards

    Friday, June 30, 2017 7:46 PM