PowerPivotPro is Coming to Boston

May 15 - 17, 2018


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

MAY 15 - 16

Foundations: Power Pivot & Power BI

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!


  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!


  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!


  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
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