Sum multiple columns in PQ

B

beagleton

New member
Joined
Jun 28, 2017
Messages
26
Reaction score
0
Points
0
So, this should be relatively simple, but for some reason I can't seem to figure it out. I have 7 columns in PQ, each with numeric values formatted as whole numbers. I need to create a custom column that contains the sum of the original 7 columns.

If I only use two columns I can easily do =[column.1]+[column.2] and it works fine. But if I do =[column.1]+[column.2]+[column.3] ... I get nothing. I have also tried:

List.Sum([column1.],[column.2],[column.3]) but all I get is errors. The one I see the most is "cannot convert null to type list". Anyway, I have spent way too long on this and yet have gotten nowhere. Any help is appreciated.
Brendan
 
You've got a null value somewhere in your data. That causes PQ to return null for any calculation that picks up a null.

One way of dealing with this is to select all the numeric columns and Replace Values, replacing null with 0. Then run your math and it will work.
 
Alternatively - you can use construction like this below

= Table.AddColumn(YourPreviousStep, "Sum", each List.Sum(Record.ToList(_)))

You do not need to change the null values
 
Bill, that's a way better solution. Nulls and zeros are, of course, very different things. Data points should be able to be null (meaning empty) so that they don't get picked up in subsequent calculations.

I only wish there was an easy UI driven way to do that, rather than having to bash out a List.Sum(Record.ToList()) function that uses an _ as a parameter. No user coming to the tool will be able to figure that out.
 
In this instance, 0 has a value (it enumerates to "normal"), so changing the nulls to zero won't really work ... in the above example:


"= Table.AddColumn(YourPreviousStep, "Sum", each List.Sum(Record.ToList(_)))"

where/how do you reference the columns you want to sum? I don't want to sum all the columns in the table, only certain ones.
 
Ken Puls said:
No user coming to the tool will be able to figure that out.
Click to expand...

Hi Ken :)
I understand your point of view but ... think.
Are the users know Vlookup function in excel? and IF function? Can they do nested functions?
Here (in PQ) is the same situation. It is impossible to get all possible combinations of functions from UI ( the same situation in excel). So, if some user want to get POWER of PQ then he has to know M (this is not as hard as it looks like) :)

Regards
 
Bill Szysz said:
Are the users know Vlookup function in excel? and IF function? Can they do nested functions?
Here (in PQ) is the same situation. It is impossible to get all possible combinations of functions from UI ( the same situation in excel).
Click to expand...


True but why shouldn't you be able to select the four/five columns you want (by holding shift or control), then just to to Add Column --> Standard --> Sum to sum across.

Oh... my... so we don't have Sum. But we do have Add or Multiply. And the code created is:
Code: 
= Table.AddColumn(#"Changed Type", "Sum", each List.Sum({[Col2], [Col3], [Col4], [Col5]}), Int64.Type)

So not exactly what you have as the columns were hard coded, but still, pretty close! And it works with the nulls too. :)
 

Attachments

  • Sample.xlsx
    17.7 KB · Views: 112
Thank you both very much. Once again, this forum has solved my problem.

Bill, unfortunately my data is made up of restricted information so I can't post ... I will see if I can mask some values or make a similar sample file without the restricted info and upload once I leave work.
 
List.Sum

This was the perfect solution to my issue - although I wasn't trying to write to a list.. the context of list.sum worked wonderfully! Thank you for sharing this so simply! Here is the formula I used in case anyone needs more assistance. = Table.AddColumn(#"Removed Columns", "SUM T24 Order $", each List.Sum({[#"T12 Order $"],[#"T13-24 Order $"]}))

beagleton said:
In this instance, 0 has a value (it enumerates to "normal"), so changing the nulls to zero won't really work ... in the above example:


"= Table.AddColumn(YourPreviousStep, "Sum", each List.Sum(Record.ToList(_)))"

where/how do you reference the columns you want to sum? I don't want to sum all the columns in the table, only certain ones.
Click to expand...
 
Bill Szysz said:
Alternatively - you can use construction like this below

= Table.AddColumn(YourPreviousStep, "Sum", each List.Sum(Record.ToList(_)))

You do not need to change the null values
Click to expand...


Hi, an old thread...but

Similarly to the List.Sum I have a table where I can have a dynamic number of columns I need to multiply.

I have a list of the dynamic column names called "ListMultiply", and am desperately trying to get it to add a column and only multiply by those columns defined in the list "ListMultiply"

=List.Product(Record.List(_,"ListMultiply"))


What am I doing wrong??
 
Sorry I meant to post

=List.Product(Record.ToList(_,"ListMultiply"))
 
It’s fine i figured it out in the end and used List.Skip to ignore the first column (index).
 
You must log in or register to reply here.
Back
Top