Replicate COUNTIFS in Power Query ("M")

[Veritan]

Hi all,

I've searched around a bunch, but have so far been unable to find a solution. I am trying to replicate the COUNTIFS formula in the Power Query M language. I've gone through several articles, and I keep running into some kind of roadblock with each one. My goal is to replicate the results in the CountIfs column below, but do so using Power Query instead of a formula. Does anyone know how to accomplish this? The best search result that I've found so far was at StackOverflow, but it seems to only be applicable to COUNTIF, and not to COUNTIFS. I would prefer to keep the Company and Subledger data types as Integers, but I'm willing to convert them to text if that is necessary. Any suggestions are greatly appreciated!

Sample Data Set:

ICO Remeasurement Tool Test.xlsm
	A	B	C	D
1	Object Account	Company	Subledger	CountIfs
2	1240.	155	170	4
3	1240.	155	315	4
4	1240.	155	385	4
5	1240.	155	421	4
6	1240.	170	155	2
7	1240.	170	240	2
8	1240.	240	170	4
9	1240.	240	315	4
10	1240.	240	385	4
11	1240.	240	421	4
12	1240.	312	314	0
13	1240.	314	315	2
14	1240.	315	155	3
15	1240.	315	240	3
16	1240.	315	314	3
17	1240.	385	155	2
18	1240.	385	240	2
19	1240.	421	155	2
20	1240.	421	240	2
21	1241.	160	185	11
22	1241.	160	195	11
23	1241.	160	196	11
24	1241.	160	205	11
25	1241.	160	210	11
26	1241.	160	291	11
27	1241.	160	300	11
28	1241.	160	301	11
29	1241.	160	305	11
30	1241.	160	310	11
31	1241.	160	375	11
32	1241.	160	410	11
33	1241.	172	240	2
34	1241.	172	245	2
35	1241.	185	160	2
36	1241.	185	312	2
37	1241.	195	160	2
38	1241.	195	312	2
39	1241.	196	160	1
40	1241.	205	160	2
41	1241.	205	245	2
42	1241.	210	160	2
43	1241.	210	312	2
44	1241.	240	172	2
45	1241.	240	386	2
46	1241.	245	172	3
47	1241.	245	205	3
48	1241.	245	386	3
49	1241.	291	160	2
50	1241.	291	312	2
51	1241.	300	160	2
52	1241.	300	312	2
53	1241.	305	160	2
54	1241.	305	312	2
55	1241.	310	160	2
56	1241.	310	312	2
57	1241.	312	185	9
58	1241.	312	195	9
59	1241.	312	210	9
60	1241.	312	291	9
61	1241.	312	300	9
62	1241.	312	305	9
63	1241.	312	310	9
64	1241.	312	410	9
65	1241.	315	312	0
66	1241.	375	160	1
67	1241.	386	240	2
68	1241.	386	245	2
69	1241.	410	160	2
70	1241.	410	312	2
Raw Data Table (2)

Cell Formulas
Range	Formula
D2:D70	D2	=COUNTIFS([Object Account],[@[Object Account]],[Subledger],[@Company])

 

[JGordon11]

= Table.AddColumn(Source, "Countif", each Table.RowCount(Table.SelectRows(Source, (s)=> (s[Object Account] = _[Object Account]) and (s[Company] = _[Subledger]))))

 

[Veritan]

Thanks for the response, JGordon. However, I don't think it's working correctly. I've checked the results and they're different than what are showing in my CountIfs column. Do you know what might be causing that? This looks really close, just not quite what I need.

 

[Veritan]

Actually, that worked perfectly. All I had to do was switch the Subledger and Company fields and it gave me exactly what I needed. Thank you!!