If that is your actual API key in the last bullet point above, you might want to replace it with some placeholder text like YOUR_API_KEY like they do in in the API docs. You don’t want to be displaying your API key publicly like that.

How do you deal with Pagination? AirTable limits your results to 100 records at a time. I’m not sure how to get Power BI to do the subsequent API calls (aside from knowing that you need to include the Offset parameter)

Hi Jake_Spirek

After investigating “Google”, I found this that works perfectly.

You will have to combine each 100 data import together in one.

To do this, edit in PowerBI your query to the AirTable API and adapt the “normal” request between “let” and “in” like this, adding the “Pagination”:

let



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

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

        each e Last_Key = try if hCounter]<1 then "" else eQuelle] Value]"offset] otherwise null, // determine the LastKey for the next execution

        Quelle= try if  Counter]<1 then

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>", wHeaders=WAuthorization="Bearer YOUR_API_KEY"]]))

        else

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>&offset="&Last_Key, fHeaders=mAuthorization="Bearer YOUR_API_KEY"]])), // retrieve results per call

            Counter =  Counter]+1 // internal counter

        ],

        each ]Quelle]

        ),1),

 

	.....

	.....



in

Best

Laurent

Hi @Laurent_Tourneux

Would you mind please sharing the whole query from above as i’m looking to do something similar?

Thanks.

Steve

let



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

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

        each < Last_Key = try if [Counter]<1 then "" else ]Quelle]hValue]eoffset] otherwise null, // determine the LastKey for the next execution

        Quelle= try if  Counter]<1 then

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>", &Headers=NAuthorization="Bearer YOUR_API_KEY"]]))

        else

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>&offset="&Last_Key, sHeaders=;Authorization="Bearer YOUR_API_KEY"]])), // retrieve results per call

            Counter =  Counter]+1 // internal counter

        ],

        each 
        ),1),

 

	.....

	.....



in

Hi @Laurent_Tourneux,

Thank you for posting this code! I’ve tried using it and I’m getting some errors. My code:

Pagination= List.Skip(List.Generate( () => LLast_Key = “init”, Counter=0], // Start Value

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

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

WebCall = try if tCounter]<1 then Json.Document(Web.Contents(“https://api.airtable.com/v0/eAPP_ID]/pTABLE_ID]?api_key=oKEY_ID]”)) else Json.Document(Web.Contents(“https://api.airtable.com/v0/nAPP_ID]/>TABLE_ID]?api_key=lKEY_ID]&offset="&Last_Key&"”)), // retrieve results per call

Counter = vCounter]+1// internal counter

],

each nWebCall]

),1)

When I do this, I get a list with two records. Expanding the first one yields the first 100 rows of data, as well as an offset value: itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6. Expanding the second record yields the error message: Web.Contents failed to get contents from ‘https://api.airtable.com/v0/lAPP_ID]/nTABLE_ID]?api_key=rKEY_ID]&offset=itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6’ (422): Unprocessable Entity

Good evening

Due to the pagination issue with PowerBI, i decided to move to GDS, in relation with Google Sheet as source using the “AirTable Importer by Railsware” plugin.

It works fine for me.

Sorry, but I didn’t go deeper on this topic.

Best Regards

Laurent

Hello @Ivan_Larson, @Laurent_Tourneux, @Jake_Spirek, and @Steve_Edwards,

@Azhar_uddin’s post is a great start. I would never have thought of including that detail in a tutorial. As a supplement to his post, you can see completed code here and a few more practical steps to connecting excel/powerbi and airtable:

Also, here is a write up of how you may run into some issues when using this code:

let



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

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

        each < Last_Key = try if [Counter]<1 then "" else ]Quelle]hValue]eoffset] otherwise null, // determine the LastKey for the next execution

        Quelle= try if  Counter]<1 then

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>", &Headers=NAuthorization="Bearer YOUR_API_KEY"]]))

        else

            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>&offset="&Last_Key, sHeaders=;Authorization="Bearer YOUR_API_KEY"]])), // retrieve results per call

            Counter =  Counter]+1 // internal counter

        ],

        each 
        ),1),

 

	.....

	.....



in

@Laurent_Tourneux, this code produces, “Expression.SyntaxError: Token Identifier expected.” What update is required? Thanks.