List.Generate in Power Query - Tutorial with Easy Examples

This video explains how List.Generate works in Power Query. You will see examples for basic number sequences, examples involving multiple variables and lastly series returning text values.
WRITTEN BLOGPOST:
gorilla.bi/power-query/list-g...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
czcams.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
Chapters:
00:00 Introduction
00:48 List.Generate Basic Number Series
08:26 List.Generate Fibonacci Sequence
14:14 List.Generate Sequence with Factorials
17:41 List.Generate Return Text Values
#ListGenerate #PowerQuery #BIGorilla

[@BIGorilla]

To learn more about the syntax of List.Generate, make sure to check out: powerquery.how/list-generate/

[@RobertoStaltari]

This is the one and only video where you can understand List.Generate. Microsoft documentation is surprisingly bare, inadequate to explain this complex and powerful function. Thanks Rick.

[@joaorataoo]

I loved the calm and clear way you explained.
Excellent!
Thank you very much!

[@CAKimberlyLewis]

Truly appreciate the written blog post partner to the video! Truly helpful to have them both! I know the written posts take so much time, but they are very appreciated.

[@BIGorilla]

Thank you so much Kimberly for your kind words. I know this function was a tough cooky for me to understand and hopefully my research gives you a kickstart.
Keep crushing it,
Rick

[@vaibhavsrivastava7196]

Thanks Rick clarifying this in calm and easy way.

[@flamethrower5320]

Thank you for the blog and video, really appreciate it.

[@vashisht1]

Thank you Rick for explaining List.Generate function in depth....love it cheers

[@BIGorilla]

Glad to hear that, awesome!

[@samirvaghasiya9918]

Extremely useful video. Thanks

[@JanBolhuis]

Great video and explanation. Thank you.

[@stewart5136]

Almost gave up on PQ until I saw this - fantastic and very powerful! Thank you. Subscribed and looking forward to watching your videos. 👍👍

[@BIGorilla]

Awesome. And thank you, welcome aboard!👏

[@suki9860]

Elegant, Elegant, Elegant! Simply magic, Rick! Thanks!

[@BIGorilla]

My pleasure suki 😁😁

[@isidrebague3659]

Simply great. Thansk for sharing

[@BIGorilla]

🙏 thank you!

[@vinayak254]

Great one sir !!. Thank you

[@odel_leal]

Súper interesting your video. Thank you.

[@wayneedmondson1065]

Awesome! Just found your channel and subscribed. Looking forward to learning more. Thumbs up!!

[@BIGorilla]

Thanks wayne! :)

[@tonyagamata2874]

Thanks! I'm still learning PBI and currently having a roadblock on a report I'm working on. I'll definitely utilise this function cos it might get me somewhere and see how it goes.
Muchas gracias !!!

[@BIGorilla]

Glad to hear that. It's an advanced function but with some examples I hope you'll manage. 😁

[@tonyagamata2874]

Don't worry @@BIGorilla, the logic and the examples were clearly explained in your vid👍🏾
Thanks again!

[@rajanidumpa9834]

Thanks for sharing the video. I have one question though, I am unable to schedule refresh with the current code. Can you please help me here, on what do I change to schedule refresh on service.

[@rrrraaaacccc80]

Excellent 💯👍

[@nguyenchitrung8809]

Thanks you

[@user-yo2nb4ry7i]

Хороший контент. Спасибо.

[@fajnefajne4339]

This video is luxurious. Thank you very much. :-)

[@BIGorilla]

Only the best for you fajne!

[@julie_chen]

Saved it !

[@fahadea1]

Genius 😮

[@ThepExcel]

Nice tutorial

[@BIGorilla]

Thanks!

[@iankr]

Thanks.
In the Fibonacci example, why are the x and y sometimes enclosed in [ ] and sometimes they are not?

[@BIGorilla]

In one part it's the opening of a record and in the other part the brackets reference the variable that was defined earlier. Hope that helps :)

[@karimallahwala7022]

Very nice und helpful content

[@BIGorilla]

Hope you find it useful!

[@teaking1981]

Amazing video showing list generate its a function that isnt well documented so appreciate the video, on the part with factorial numbers should the selector for "Y" also be Y=[X]=1 as on that loop it would have been using X as the initial value so you get the duplicate line with 1? Im only asking to try to understand the function :) I really enjoyed the video

[@BIGorilla]

Hi Glyn,
That's a good point. You could improve the code by starting with Y = 1, and starting with x = 2. This would mean that the first returned result is Y = 1. The next value then is the Number.Factorial( 2), so for X = 2. With that change, the result would be a list showing:
{1, 2, 6, 24, 120, 720, 5040, 40320, 362880 }
Or the full code:
= List.Generate(
() => [x = 2, y = 1], // x = increasing series, y = factorial
each [x]

[@teaking1981]

@@BIGorilla brilliant thanks for the explanation!

[@DinoAMAntunes]

Hello Good Night, Can you share a video with a table with 100 rows and 3 columns for example, and you want to split that table in 10 sub-tables, automatically, and 10 new excel sheets. tks in advance.

[@bn_ln]

In the factorial example shouldn't it be y = Number.Factorial ( x ) ---ie. not [x] otherwise the two lists are not the same

[@Fatemeh-tu1vc]

Hi there, I used your instruction but once I apply the changes it takes 20 hrs. so data be loaded and I have 1200000 rows of data. can you please help me out find the problem?