Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PMT Function in Power Query

PMT function exists in DAX but I need to do this calculation in Power Query. 

Does anyone have a workaround for this?

4 REPLIES 4
edhans
Super User
Super User

You can either do the math manually, or write a custom function. Here is a function that does it:

(PV, r, n) =>
let
    Payment = (r * PV) / (1 - Number.Power(1 + r, -n))
in
    Payment
  • Create a new blank query.
  • Open the Advanced Editor
  • Erase everything.
  • Paste in the above formula
  • Rename the query (probably Query1) to fnPMT (or whatever you want to call it, I just like custom functions to start with fn*

Now just use it like normal.

I added a custom column and typed = fnPMT([Present Value], [Rate], [Term]) and it did the work.

edhans_0-1620179900842.png

I used the base formula from here @Anonymous - you may need to divide your rate by the periods in a year depending on how your data is laid out. In that case it would be fnPMT([Present Value], [Rate]/12, [Term])



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

The difference between DAX's PMT calculation and this is that this does not consider Future VAlue (FV) in the formula. 

What this helpful @Anonymous ? Any questions?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@Anonymous You can find the formula for PMT here: P-Q Excel to DAX Translation - Microsoft Power BI Community Should be able to translate it to M since it is all base arithmatic.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors