Announcement Update: For those who missed out on the webinar
- Watch the Session Recording for my (Avi Singh) webinar. Click here to watch the session recording of all PASS BA webinars.
- Download the PowerPoint Slide-Deck, Power BI Desktop (.pbix) file, Excel 2013 file
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:
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:
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?
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:
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!
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
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
Get in touch with a P3 team member