Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
priceless88
Frequent Visitor

List.Generate an array of JSON docs into a single table

I have a Database Lite apllication called Airtable linked via its api into Power BI. The Airtable API has 100 record limit on each pull, so you have to paginate on subsequent calls. I figured this out with a little google help. However, I now have a list of 7 pages for a total of 679 entires. I did figure out how to join them all into a single table by hand, but I would like to reproduce this on any Airtable Base I call. That means the amount of records that are called might be 50 or might be 1200, so thats one page to 12 pages. My understanding is that List.Generate will handle this if I know how to construct it. This is literally day two looking at the syntax for all of Power Bi, so I need some help understanding haha. Here's how I did it without list.generate. Sorry if its a mess.

 

Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value

                                each  [Last_Key] <> null, // Condition under which the next execution will happen

                                each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution

                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<mytable>/Budget%20Descriptions%20and%20Cost?",[Headers=[Authorization="... <mykey>"]]))else Json.Document(Web.Contents("https://api.airtable.com/v0/<mytable>/Budget%20Descriptions%20and%20Cost?offset="&Last_Key,[Headers=... <mykey>"]])), // retrieve results per call

                                       Counter = [Counter]+1// internal counter

                      ],

                                each [WebCall]

    ),1),

#"Combined to Table" = Table.FromRecords({Pagination{0},Pagination{1},Pagination{2},Pagination{3},Pagination{4},Pagination{5},Pagination{6}})

in

#"Combined to Table"

 

Edit 1

Edited out the block of code because I realized it was mostly extranious. I just want to turn that "Table.FromRecords" into a List.Generate statement that can process a variable amount of "Pagination".

   

1 ACCEPTED SOLUTION

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

Hi priceless88,

 

About how to achieve data from ari, you can refer to this blog: https://powerpivotpro.com/2016/02/reviewlist-generate-create-tables-thin-air-power-bi-m/.

 

Regards,

Jimmy Tao

I appreciate the reference. I understand the basics of the way it works though, so generating a list of digits wasn't that exciting of an experience. I edited down my OP to get rid of all the broken out tables since I don't need that yet.

Figured it out.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.