Is there a SUMIFS equivalent in Power Query?

[JVL]

Is there an equivalent for SUMIFS formula in Power Query? I would like to calculate the yellow column in Power Query which includes a cumulative sum for each account for each month and year. Grouping the columns does not work as I want to have both the monthly and the cumulative value in the data. How do I do this?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddColumn(Source, "YTD", (x)=> List.Sum(Table.SelectRows(Source, each _[Account] = x[Account] and _[Month]<=x[Month])[Value]))
in
    tbl

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Year	Month	Value		Account	Year	Month	Value	YTD
2	A	2020	1	100		A	2020	1	100	100
3	A	2020	2	100		A	2020	2	100	200
4	A	2020	3	100		A	2020	3	100	300
5	A	2020	4	100		A	2020	4	100	400
6	A	2020	5	100		A	2020	5	100	500
7	A	2020	6	250		A	2020	6	250	750
8	A	2020	7	100		A	2020	7	100	850
9	A	2020	8	100		A	2020	8	100	950
10	A	2020	9	100		A	2020	9	100	1050
11	A	2020	10	100		A	2020	10	100	1150
12	A	2020	11	100		A	2020	11	100	1250
13	A	2020	12	100		A	2020	12	100	1350
14	B	2020	1	100		B	2020	1	100	100
15	B	2020	2	100		B	2020	2	100	200
16	B	2020	3	100		B	2020	3	100	300
17	B	2020	4	90		B	2020	4	90	390
18	B	2020	5	100		B	2020	5	100	490
19	B	2020	6	100		B	2020	6	100	590
20	B	2020	7	100		B	2020	7	100	690
21	B	2020	8	100		B	2020	8	100	790
22	B	2020	9	100		B	2020	9	100	890
23	B	2020	10	100		B	2020	10	100	990
24
Sheet2

 

[JVL]

This works perfectly! Thank you! I even managed to make YTD for different years.

Could you be able to provide a brief run through of the formula? And do you have suggestions how to calculate last twelve months if I have data for several years?

 

[JGordon11]

YW. The Table.SelectRows returns a filtered table, in this case filtered to Account= Account name in row x AND Month <= the Month in row x.
(so as it steps through the rows the filtered table will have just one row for row 0, two for row 1 ... twelve for row11, one for row 12, two for row 13, ...)

List.Sum adds up the Value column in that filtered table.

Table.AddColumn adds the YTD column consisting of each of those List.Sum values.

If you send an xl2bb (or something else that I can paste into excel, i.e., not a picture) of a sample initial table and what the transformed table should look like, I'll revise the query based on the examples.

 

[JVL]

Your help is much appreciated!

Attached is a sample of a situation I'm usually dealing. The first table is the source data which can come from external excels or source sheets in the same workbook. First I usually need to make some modifications to the data in Power Query such as separating company and account and time to years and months. Sometimes these modifications need to be more complex because I usually receive data in different formats. Only after the modifications I would need to add YTD and LTM columns to the query by company, account, year and month.

Thank you in advance!

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Time	Value		Company	Account name	Account number	Year	Month	Value	YTD by company, account, year and month	LTM by company, account, year and month2
2	Company A - Account 100	2020-01	50		Company A	Account	100	2020	1	50
3	Company A - Account 100	2020-02	20		Company A	Account	100	2020	2	20
4	Company A - Account 100	2020-03	20		Company A	Account	100	2020	3	20
5	Company A - Account 100	2020-04	20		Company A	Account	100	2020	4	20
6	Company A - Account 100	2020-05	20		Company A	Account	100	2020	5	20
7	Company A - Account 100	2020-06	20		Company A	Account	100	2020	6	20
8	Company A - Account 100	2020-07	20		Company A	Account	100	2020	7	20
9	Company A - Account 100	2020-08	100		Company A	Account	100	2020	8	100
10	Company A - Account 100	2020-09	20		Company A	Account	100	2020	9	20
11	Company A - Account 100	2020-10	20		Company A	Account	100	2020	10	20
12	Company A - Account 100	2020-11	20		Company A	Account	100	2020	11	20
13	Company A - Account 100	2020-12	20		Company A	Account	100	2020	12	20
14	Company A - Account 200	2020-01	40		Company A	Account	200	2020	1	40
15	Company A - Account 200	2020-02	40		Company A	Account	200	2020	2	40
16	Company A - Account 200	2020-03	40		Company A	Account	200	2020	3	40
17	Company A - Account 200	2020-04	40		Company A	Account	200	2020	4	40
18	Company A - Account 200	2020-05	40		Company A	Account	200	2020	5	40
19	Company A - Account 200	2020-06	40		Company A	Account	200	2020	6	40
20	Company A - Account 200	2020-07	(200)		Company A	Account	200	2020	7	(200)
21	Company A - Account 200	2020-08	40		Company A	Account	200	2020	8	40
22	Company A - Account 200	2020-09	40		Company A	Account	200	2020	9	40
23	Company A - Account 200	2020-10	300		Company A	Account	200	2020	10	300
24	Company A - Account 200	2020-11	40		Company A	Account	200	2020	11	40
25	Company A - Account 200	2020-12	40		Company A	Account	200	2020	12	40
26	Company A - Account 100	2019-01	10		Company A	Account	100	2019	1	10
27	Company A - Account 100	2019-02	10		Company A	Account	100	2019	2	10
28	Company A - Account 100	2019-03	10		Company A	Account	100	2019	3	10
29	Company A - Account 100	2019-04	10		Company A	Account	100	2019	4	10
30	Company A - Account 100	2019-05	10		Company A	Account	100	2019	5	10
31	Company A - Account 100	2019-06	10		Company A	Account	100	2019	6	10
32	Company A - Account 100	2019-07	10		Company A	Account	100	2019	7	10
33	Company A - Account 100	2019-08	50		Company A	Account	100	2019	8	50
34	Company A - Account 100	2019-09	10		Company A	Account	100	2019	9	10
35	Company A - Account 100	2019-10	10		Company A	Account	100	2019	10	10
36	Company A - Account 100	2019-11	10		Company A	Account	100	2019	11	10
37	Company A - Account 100	2019-12	10		Company A	Account	100	2019	12	10
38	Company A - Account 200	2019-01	20		Company A	Account	200	2019	1	20
39	Company A - Account 200	2019-02	20		Company A	Account	200	2019	2	20
40	Company A - Account 200	2019-03	20		Company A	Account	200	2019	3	20
41	Company A - Account 200	2019-04	20		Company A	Account	200	2019	4	20
42	Company A - Account 200	2019-05	20		Company A	Account	200	2019	5	20
43	Company A - Account 200	2019-06	20		Company A	Account	200	2019	6	20
44	Company A - Account 200	2019-07	(100)		Company A	Account	200	2019	7	(100)
45	Company A - Account 200	2019-08	20		Company A	Account	200	2019	8	20
46	Company A - Account 200	2019-09	20		Company A	Account	200	2019	9	20
47	Company A - Account 200	2019-10	150		Company A	Account	200	2019	10	150
48	Company A - Account 200	2019-11	20		Company A	Account	200	2019	11	20
49	Company A - Account 200	2019-12	20		Company A	Account	200	2019	12	20
50	Company B - Account 100	2020-01	80		Company B	Account	100	2020	1	80
51	Company B - Account 100	2020-02	80		Company B	Account	100	2020	2	80
52	Company B - Account 100	2020-03	80		Company B	Account	100	2020	3	80
53	Company B - Account 100	2020-04	80		Company B	Account	100	2020	4	80
54	Company B - Account 100	2020-05	80		Company B	Account	100	2020	5	80
55	Company B - Account 100	2020-06	80		Company B	Account	100	2020	6	80
56	Company B - Account 100	2020-07	80		Company B	Account	100	2020	7	80
57	Company B - Account 100	2020-08	400		Company B	Account	100	2020	8	400
58	Company B - Account 100	2020-09	80		Company B	Account	100	2020	9	80
59	Company B - Account 100	2020-10	80		Company B	Account	100	2020	10	80
60	Company B - Account 100	2020-11	80		Company B	Account	100	2020	11	80
61	Company B - Account 100	2020-12	80		Company B	Account	100	2020	12	80
62	Company B - Account 200	2020-01	160		Company B	Account	200	2020	1	160
63	Company B - Account 200	2020-02	160		Company B	Account	200	2020	2	160
64	Company B - Account 200	2020-03	160		Company B	Account	200	2020	3	160
65	Company B - Account 200	2020-04	160		Company B	Account	200	2020	4	160
66	Company B - Account 200	2020-05	160		Company B	Account	200	2020	5	160
67	Company B - Account 200	2020-06	160		Company B	Account	200	2020	6	160
68	Company B - Account 200	2020-07	(800)		Company B	Account	200	2020	7	(800)
69	Company B - Account 200	2020-08	160		Company B	Account	200	2020	8	160
70	Company B - Account 200	2020-09	160		Company B	Account	200	2020	9	160
71	Company B - Account 200	2020-10	1,200		Company B	Account	200	2020	10	1,200
72	Company B - Account 200	2020-11	160		Company B	Account	200	2020	11	160
73	Company B - Account 200	2020-12	160		Company B	Account	200	2020	12	160
74	Company B - Account 100	2020-01	60		Company B	Account	100	2020	1	60
75	Company B - Account 100	2020-02	60		Company B	Account	100	2020	2	60
76	Company B - Account 100	2020-03	60		Company B	Account	100	2020	3	60
77	Company B - Account 100	2020-04	60		Company B	Account	100	2020	4	60
78	Company B - Account 100	2020-05	60		Company B	Account	100	2020	5	60
79	Company B - Account 100	2020-06	60		Company B	Account	100	2020	6	60
80	Company B - Account 100	2020-07	60		Company B	Account	100	2020	7	60
81	Company B - Account 100	2020-08	120		Company B	Account	100	2020	8	120
82	Company B - Account 100	2020-09	60		Company B	Account	100	2020	9	60
83	Company B - Account 100	2020-10	60		Company B	Account	100	2020	10	60
84	Company B - Account 100	2020-11	60		Company B	Account	100	2020	11	60
85	Company B - Account 100	2020-12	60		Company B	Account	100	2020	12	60
86	Company B - Account 200	2019-01	40		Company B	Account	200	2019	1	40
87	Company B - Account 200	2019-02	40		Company B	Account	200	2019	2	40
88	Company B - Account 200	2019-03	40		Company B	Account	200	2019	3	40
89	Company B - Account 200	2019-04	40		Company B	Account	200	2019	4	40
90	Company B - Account 200	2019-05	40		Company B	Account	200	2019	5	40
91	Company B - Account 200	2019-06	40		Company B	Account	200	2019	6	40
92	Company B - Account 200	2019-07	40		Company B	Account	200	2019	7	40
93	Company B - Account 200	2019-08	200		Company B	Account	200	2019	8	200
94	Company B - Account 200	2019-09	40		Company B	Account	200	2019	9	40
95	Company B - Account 200	2019-10	40		Company B	Account	200	2019	10	40
96	Company B - Account 200	2019-11	40		Company B	Account	200	2019	11	40
97	Company B - Account 200	2019-12	40		Company B	Account	200	2019	12	40
98	Company B - Account 100	2019-01	80		Company B	Account	100	2019	1	80
99	Company B - Account 100	2019-02	80		Company B	Account	100	2019	2	80
100	Company B - Account 100	2019-03	80		Company B	Account	100	2019	3	80
101	Company B - Account 100	2019-04	80		Company B	Account	100	2019	4	80
102	Company B - Account 100	2019-05	80		Company B	Account	100	2019	5	80
103	Company B - Account 100	2019-06	80		Company B	Account	100	2019	6	80
104	Company B - Account 100	2019-07	(400)		Company B	Account	100	2019	7	(400)
105	Company B - Account 100	2019-08	80		Company B	Account	100	2019	8	80
106	Company B - Account 100	2019-09	80		Company B	Account	100	2019	9	80
107	Company B - Account 100	2019-10	600		Company B	Account	100	2019	10	600
108	Company B - Account 100	2019-11	80		Company B	Account	100	2019	11	80
109	Company B - Account 100	2019-12	80		Company B	Account	100	2019	12	80
110	Company B - Account 100	2019-01	30		Company B	Account	100	2019	1	30
111	Company B - Account 100	2019-02	30		Company B	Account	100	2019	2	30
112	Company B - Account 100	2019-03	30		Company B	Account	100	2019	3	30
113	Company B - Account 100	2019-04	30		Company B	Account	100	2019	4	30
114	Company B - Account 100	2019-05	30		Company B	Account	100	2019	5	30
115	Company B - Account 100	2019-06	30		Company B	Account	100	2019	6	30
116	Company B - Account 100	2019-07	30		Company B	Account	100	2019	7	30
117	Company B - Account 100	2019-08	60		Company B	Account	100	2019	8	60
118	Company B - Account 100	2019-09	30		Company B	Account	100	2019	9	30
119	Company B - Account 100	2019-10	30		Company B	Account	100	2019	10	30
120	Company B - Account 100	2019-11	30		Company B	Account	100	2019	11	30
121	Company B - Account 100	2019-12	30		Company B	Account	100	2019	12	30
DATA

 

[JGordon11]

try (make sure Value column name is "Value" and not " Value ")

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split1 = Table.SplitColumn(Source, "Item", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Company", "AccountName"}),
    Split2 = Table.SplitColumn(Split1, "AccountName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"AccountName", "AccountNumber"}),
    Split3 = Table.SplitColumn(Split2, "Time", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Year", "Month"}),
    tblType = Table.TransformColumnTypes(Split3,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Value", type number}}),
    tbl = Table.AddColumn(tblType, "YTD", (x)=> List.Sum(Table.SelectRows(tblType, each _[Company] = x[Company] and _[AccountNumber] = x[AccountNumber] and 
        _[Year]=x[Year] and  _[Month]<=x[Month])[Value])),
    tbl1 = Table.AddColumn(tbl, "LTM", (x)=> List.Sum(Table.SelectRows(tbl, each _[Company] = x[Company] and _[AccountNumber] = x[AccountNumber] and 
        #date(_[Year],_[Month],1) <=#date(x[Year],x[Month],1) and #date(_[Year]+1,_[Month],1)> #date(x[Year],x[Month],1))[Value]))
in
    tbl1

 

[JVL]

This is excellent and works perfectly! Thank you!

 

[danielphayward]

~2 years later this solution still works.

I have a hard time understanding what the (x)=> and x in the formula do, but it works for me ¯\_(ツ)_/¯

 

[smozgur]

~2 years later this solution still works.

I have a hard time understanding what the (x)=> and x in the formula do, but it works for me ¯\_(ツ)_/¯

The Table.AddColumn() function has two parameters. The first one is a table value to which the new column will be added, and the second one is a function that generates the new column values.

In M language, you can define a function like this, where 'a' and 'b' are the function parameters:

(a, b) => a + b

The proper version of the same function with value types specified is:

(a as number, b as number) as number => a + b

Like many other native functions in the M language, function closures are used to iterate through the given source's elements. It takes each element of the provided source value (a table and its rows in this case) and uses each item to create the new structure (a column here). The iterator function takes this element as its only parameter. In this case, 'x' represents the table rows for the Table.AddColumn() function. Each 'x' in the function refers to the row in the current iteration.

Take a look at the following simple query, which takes a list and adds 1 to each item of the list to create an updated list with the Result identifier:

let
    Source = {1,2,3},
    Result = List.Transform(Source, (x) => x + 1)
in
    Result

You would often see an "each" expression and an underscore instead. The following query is exactly the same as the previous one, except the "each" expression is used as a shorthand and underscore instead of 'x' below. This is because the generator function in the host function (List.Transform()) is an undeclared function closure that requires a single parameter (list items in this case) defined by the host function in its own scope. This single parameter is represented by the underscore when the "each" expression is used:

let
    Source = {1,2,3},
    Result = List.Transform(Source, each _ + 1)
in
    Result

In the code that answered this question (by @JGordon11), the (x) => notation is used because there is an inner function, Table.SelectRows(), that will recognize the underscore as its own parameter in its own scope. To access the current row's Account record field in the second function, x must be explicitly passed as the function parameter.

tbl = Table.AddColumn(Source, "YTD", (x)=> List.Sum(Table.SelectRows(Source, each _[Account] = x[Account] and _[Month]<=x[Month])[Value]))

I hope this explanation hasn't made it more complicated, but this is the short story of generator/iterator functions and hopefully, it helps.

 

[danielphayward]

It's going to take me some time to read through that enough times to get it fully, but manalive that was more than I expected! Thank you!