PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!


**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!


  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms


Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

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.

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:

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

The above code will simply return the number 1.

I could also write a function to return a record

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

The output will be a record that looks like this:


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 }

Interested in Learning How to Do this Kind of Thing?

power query

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

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:



  • 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)=>


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

Payments =

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)       


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!


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 18 Comments

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

    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:


    P = 250000,
    n = 360,

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

    Payments = Table.FromList(
    ()=>[Counter=0, Balance=P, MonthlyInterest=0, MonthlyPrincipal=0, MonthlyPayment=0],
    each [Counter]<n,
    each [ Counter=[Counter]+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"})

    #"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.




    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
      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,


  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…


    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.


  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