converting Excel formulas to Power Query M Language

[mdshuja]

Hello, could someone kindly assist me in converting Excel formulas to Power Query M Language?, I have a lot of data in Excel—more than 500,000 transactions.
here is the formula.

=IF(AND(SUMIFS(M:M,A:A,A2,B:B,B2)>0,COUNTIFS(A$2:A2,A2,B$2:B2,B2,Q$2:Q2,Q2)=1),MAXIFS(G:G,A:A,A2,B:B,B2,Q:Q,Q2),0)

Thanks
Rahman.

 

[jdellasala]

I suggest you post a small sample data table using XL2BB.

 

[mdshuja]

Sample.XLSX
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Policy No.	Date	Policy Date	Branch	First Name	Last Name	Premium	C Premium	PF	AP	IAP	MaxValue	Revenue	Product Code	Product Name	Process Date	AC
2	8524725	12/12/2022	12/01/2021	MAA	Murugan	Kumar	1,750.00		Quarterly	17,188.00	17,188.00	1,750.00	150.00	AC01	Accident	12/25/2022	548348
3	8524725	12/12/2022	12/01/2021	MAA	Murugan	Kumar	1,200.00		Quarterly	17,188.00	17,188.00	2,547.00	120.00	AC01	Accident	12/25/2022	542835
4	8524725	12/12/2022	12/01/2021	MAA	Murugan	Kumar	1,500.00		Quarterly	17,188.00	17,188.00	-	175.00	AC01	Accident	12/25/2022	548348
5	8524725	12/12/2022	12/01/2021	MAA	Murugan	Kumar	2,547.00		Quarterly	17,188.00	17,188.00	-	254.70	AC01	Accident	12/25/2022	542835
6	8524725	12/18/2022	12/01/2021	MAA	Murugan	Kumar	1,500.00		Quarterly	17,188.00	17,188.00	1,750.00	150.00	AC01	Accident	12/25/2022	548348
7	8524725	12/18/2022	12/01/2021	MAA	Murugan	Kumar	1,200.00		Quarterly	17,188.00	17,188.00	2,547.00	120.00	AC01	Accident	12/25/2022	542835
8	8524725	12/18/2022	12/01/2021	MAA	Murugan	Kumar	1,750.00		Quarterly	17,188.00	17,188.00	-	175.00	AC01	Accident	12/25/2022	548348
9	8524725	12/18/2022	12/01/2021	MAA	Murugan	Kumar	2,547.00		Quarterly	17,188.00	17,188.00	-	254.70	AC01	Accident	12/25/2022	542835
Sheet 1

Cell Formulas
Range	Formula
L2:L9	L2	=IF(AND(SUMIFS(M:M,A:A,A2,B:B,B2)>0,COUNTIFS(A$2:A2,A2,B$2:B2,B2,Q$2:Q2,Q2)=1),MAXIFS(G:G,A:A,A2,B:B,B2,Q:Q,Q2),0)

 

[mdshuja]

Hi jdellasala,​

if you have time please check & reply thanks.