converting Excel formulas to Power Query M Language

mdshuja

New Member
Joined
Nov 11, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I suggest you post a small sample data table using XL2BB.
 
Upvote 0
Sample.XLSX
ABCDEFGHIJKLMNOPQ
1Policy No.DatePolicy DateBranchFirst NameLast NamePremiumC PremiumPFAPIAP MaxValue RevenueProduct CodeProduct NameProcess DateAC
2852472512/12/202212/01/2021MAAMuruganKumar1,750.00Quarterly17,188.0017,188.001,750.00150.00AC01Accident12/25/2022548348
3852472512/12/202212/01/2021MAAMuruganKumar1,200.00Quarterly17,188.0017,188.002,547.00120.00AC01Accident12/25/2022542835
4852472512/12/202212/01/2021MAAMuruganKumar1,500.00Quarterly17,188.0017,188.00-175.00AC01Accident12/25/2022548348
5852472512/12/202212/01/2021MAAMuruganKumar2,547.00Quarterly17,188.0017,188.00-254.70AC01Accident12/25/2022542835
6852472512/18/202212/01/2021MAAMuruganKumar1,500.00Quarterly17,188.0017,188.001,750.00150.00AC01Accident12/25/2022548348
7852472512/18/202212/01/2021MAAMuruganKumar1,200.00Quarterly17,188.0017,188.002,547.00120.00AC01Accident12/25/2022542835
8852472512/18/202212/01/2021MAAMuruganKumar1,750.00Quarterly17,188.0017,188.00-175.00AC01Accident12/25/2022548348
9852472512/18/202212/01/2021MAAMuruganKumar2,547.00Quarterly17,188.0017,188.00-254.70AC01Accident12/25/2022542835
Sheet 1
Cell Formulas
RangeFormula
L2:L9L2=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)
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top