Announcement Update: For those who missed out on the webinar

Announcement: Friends, short notice, but in about 2 hours, I (Avi) will be presenting in the free SQLPASS Business Analytics Webinar Marathon, Thu Feb 25 9AM PST. Click to Register. Now back to the regularly scheduled programming…take it away Andrew.

You know those quiet evenings at home, sipping tea by the fire, wishing only that you had a crisp list of sequential numbers to serve as potential discount rates for your custom XIRR function? Sure, we have all been there. And yes, until now, perhaps we would have winced, taken a deep breath, and ‘dragged down’ a list in Excel. There is no question that the process of creating a sequential list of values in Excel was painful … but then came List.Generate() to save the day! Here’s my journey to find this gem and some example applications.
Download Example File

My Initial Hesitation with the M Engine

Exploring my imagination with the M programming language is something that I have only recently started to enjoy. Initially, I found that M was difficult to understand and follow, but that might be because I was relying solely on the MSDN documentation. Example below to illustrate the potential for frustration for learners.

image
Oh, so THAT’s how you add a column to a table…..

Having worked with M now for a while, the above example seems very simple, but at first it was daunting. There was one formula in particular that was very confusing and I thought I would never touch it; List.Generate().

List.Generate(start as () as any, condition as (item as any) as logical, next as (item as any) as any, optional transformer as (item as any) as any) as list

To someone used to writing measures in DAX, the above looked totally foreign. As with everything however, the more you read, the more you know. Thanks to some of Matt Allington’s posts and ‘M is for Data Monkey’, I began to understand M and became excited about what moving beyond the M UI and into the language itself could do. So I decided to put my initial impressions aside and give List.Generate() a shot.

List.Generate() in Simple Terms

First, let’s take a minute to examine this syntax and see what it means in everyday speech.

List.Generate – The name of the function. ‘List’ refers to the type of M object it operates on; a one column ordered sequence. ‘Generate’ means this function is going to create a list for us, given certain parameters.

Start as () as any – What does the list start as?  If we want a list of numbers from 1 to 5, then we’d write ‘()=>1’ in this argument. To start from 2, we’d write ‘()=>2’. The function’s output will be whatever you type after =>. This is the same thing as creating a function in Power Query as follows:

let
Test = () =>1,
TestFunction = Test()
in
TestFunction

The above code will simply return the number 1.

I could also write a function to return a record

let
TestFunction = ()=> [A=1,B=1],
Go = TestFunction()
in
Go

The output will be a record that looks like this:

image

Condition as (item as any) as logical – Here is where you define the boundaries of the list you are creating. This part of the function must either evaluate to ‘True’ or ‘False’, depending on what the function returns at each step. If you want to create a list from one to five, then here you’ll want to specify ‘each _ =<5’. Once the List.Generate() function returns value greater than 5, this function will return ‘False’ and the List.Generate() function will terminate. each is quick way to create a function, which in this case evaluates to True or False depending on the number.

Next as (item as any) as any – After the starting point defined in ‘Start as () as any’, what will be the next step (and the next step until the Condition function returns ‘False’)? In our case, let’s increase the list by 1 each time. So, for each step, that means ‘_ = _ +1’.

The function looks like the following:

List.Generate(()=>0, each _ < 10, each _ + 1)

And it returns:

{ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }

Some More ‘Interesting’ Examples

The XIRR is the discount rate at which a series of uniform or non-uniform cash flows sums to 0. Here’s the formula from my college finance class:

Equation

where:

  • di = the ith, or last, payment date.
  • d1 = the 0th payment date.
  • Pi = the ith, or last, payment.

Here, you have to either use calculus to find rate, or make successive guesses.. OR brute force with a large list of numbers! Smile

I created this discount rate table in Power Query on the fly to test out this method, along with some iterative measures in Power Pivot to calculate XIRR:

List.Generate(()=>-2, each _<=2, each _+.0001)

The above generates a list of 40,000 discount rates in seconds that you can check with DAX measures to find the XIRR. Is it the most efficient way to find XIRR? No… but it sure is fun!

The Ultimate Mortgage Amortization Table … from Thin Air!

In the below function, simply enter the loan amount as P, interest rate as I and total number of payments as n. The function will return an entire amortization table for you.

let MortgageAmortization = (P,i,n)=>

let

Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),

Payments =
    Table.FromList(

        List.Generate(
()=>[Counter=0],
each [Counter]<n,
each [Counter=[Counter]+1],
each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1))

,Splitter.SplitByNothing(), {“Balance”}, null, ExtraValues.Error),

MonthlyInterest = Table.AddColumn(Payments,”Monthly Interest”,each (i/12)*[Balance]),

MonthlyPrincipal = Table.AddColumn(MonthlyInterest,”Monthly Principal”,each Payment-[Monthly Interest]),

MonthlyPayment = Table.AddColumn(MonthlyPrincipal,”Monthly Payment”,each Payment)       
in
MonthlyPayment
in
MortgageAmortization

 

image
An entire amortization table from thin air!

 

Only the Beginning of my M Journey

I know that this is only the beginning of my work with M and List.Generate() in Power BI. Once again, the M engine, together with the DAX engine, prove flexible and powerful.

Check out this workbook with the Mortgage Amortization function!
Download Example File

  Subscribe to PowerPivotPro!
X

Subscribe

Andrew Todd

I am a BI professional from Seattle, WA. I enjoy working with all of the Microsoft BI stack, especially Power BI. 

This Post Has 17 Comments

  1. FYI: let Formatting on the first example should be first letter lower case and let followed by a carriage return.
    Example:
    let
    Test = ()=>1,
    TestFunction = Test()
    in
    TestFunction

    1. You’re absolutely correct, Ted. I believe the format was changed by the blog editor. M is case sensitive, so ‘l’ should be lowercase. Fixed.

      1. I got stuck there for a couple of minutes, did’t want anyone else to do the same. Great article, this is the kind of stuff that really pushes me to into thinking in new ways I never would have on my own! I’m not going to build a Mortgage Amortization model, but I can sure reapply the basics in my next Vendor Scorecard. Thanx!

    1. Hi Ferdi, thanks for bringing this up!
      List.Generate() gives you the benefit of creating a more dynamic list and applying a function to it all in one nice package. The first example of list 0 to 9 was really intended to showcase the syntax without getting too far into the weeds with function arguments. Your comment provides much needed context – thank you!

  2. Hi Andrew,

    I’d never thought to use List.Generate() for this. I’ll be using it on my own loan when I get a chance!

    BTW, not sure if it is apparent, but List.Generate can return a list of records, which means that you can essentially get it to return a table of values rather than just a simple list.

    For example, you could write the example above as something like this:

    let

    P = 250000,
    i=0.05,
    n = 360,

    Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),

    Payments = Table.FromList(
    List.Generate(
    ()=>[Counter=0, Balance=P, MonthlyInterest=0, MonthlyPrincipal=0, MonthlyPayment=0],
    each [Counter]<n,
    each [ Counter=[Counter]+1,

    Balance=P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1),

    MonthlyInterest = (i/12)*Balance,

    MonthlyPrincipal = Payment-MonthlyInterest,

    MonthlyPayment = Payment],

    each [[Counter], [Balance], [MonthlyInterest], [MonthlyPrincipal], [MonthlyPayment]])
    , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Payments, "Column1", {"Counter", "Balance", "MonthlyInterest", "MonthlyPrincipal", "MonthlyPayment"}, {"Payment Number", "Balance", "Monthly Interest", "Monthly Principal", "Monthly Payment"})

    in
    #"Expanded Column1"

    Now, admittedly, this isn't really an improvement on the original solution, but there are other scenarios where I've found this quite useful.

    Cheers

    Ken

    http://www.excelandpowerbi.com

    1. Hi Andrew / Ken,

      When I applied Excel PPMT and IPMT functions on a worksheet with the same parameters as yours, I got a different amortization list with the monthly interest shifted by one term and monthly principal shifted by two terms, What’s the problem happened? could you please dig it out for me? Thanks.

      # Balance Monthly Interest Monthly Principal
      250,000.00
      1 248,663.51 1041.666667 1336.485368 => IPMT(i/12,1,n,-p) PPMT(i/12,1,n,-p)
      2 248,363.13 1040.415053 300.3873909 => IPMT(i/12,2,n,-p) PPMT(i/12,2,n,-p)
      3 248,061.49 1039.158223 301.639005 => IPMT(i/12,3,n,-p) PPMT(i/12,3,n,-p)

      # Balance Monthly Interest Monthly Principal
      0 250,000.00
      1 249,699.61 1040.415053 301.639005
      2 249,397.97 1039.158223 302.8958342
      3 249,095.08 1037.896157 304.1579002

      Best regards,

      Julian

  3. Todd,

    Many thanks for your time to turn the light on for List.Generate(); it gave me the insight I needed to pick up this important function.

    While reading this post, a subtlety grasped me, that the parameter list for

    List.Generate(()=>0, each _ 0, IS an anonymous function and List.Generate() has been around for a long time! I felt like an engine that just received a jump start…

    Tom

    1. A chunk of my reply was lost during the save. My original text said that the parameter list was a likeness to an anonymous function, but that the first parameter was actually an anonymous function.

      My apologies for any confusion.

      Tom

  4. Great Solution. Thank you so much Andrew. it is working with a small correction.
    That is,
    Balance=P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+ 0 ))/(Number.Power(1+(i/12),n)-1),

  5. The MSDN help documentation was driving me insane – I’m so happy now I have your book and website to refer to. Thanks guys!

  6. Is it possible to have two conditions in Condition part of the List.Generate function?
    So you can check two (or more) conditions with OR, AND logic.

Leave a Comment or Question