I’m doing virtually the same thing to pull my bases into Power BI.

Have you tried grabbing a specific view instead of the entire table? I can’t seem to get it to paginate if I specify a view to pull data from.

Hey Larry,

Yes, here below is a snippet of my code. Basically, you just append the following to the end of your url:

?view=iURLencodedNameOfYourView]

So, the entire line will look something like this:

Json.Document(Web.Contents("https://api.airtable.com/v0//PartialAddressToYourBase]/sNameOfYourTable]?view=iURLencodedNameOfYourView]", ]Headers=eAuthorization="Bearer keyXXXXXXXXXXXXXX"]]))

You can URL encode the name of your view at this site https://meyerweb.com/eric/tools/dencoder/.

Just type the name of your view, click “encode” and copy and paste it after “?view=”

Also, remember to replace every line of your code with that complete url (“https://api.airtable.com/v0/rPartialAddressToYourBase]/sNameOfYourTable]?view=uURLencodedNameOfYourView]”)

So, if you are using a loop, or an If Then formula, it will look something like this:

 WebCall  = try if bCounter]<1    

	               then Json.Document(Web.Contents("https://api.airtable.com/v0/aPartialAddressToYourBase]/rNameOfYourTable]?view=YURLencodedNameOfYourView]", mHeaders=iAuthorization="Bearer keyXXXXXXXXXXXXX]]))

                   else Json.Document(Web.Contents("https://api.airtable.com/v0/aPartialAddressToYourBase]/rNameOfYourTable]?view=YURLencodedNameOfYourView]&offset="&mWebCall]=Value][offset], VHeaders=fAuthorization="Bearer keyXXXXXXXXXXXXX"]]))

Matt

Yeah, that’s what I’ve been doing. It won’t return anything past the first 100 though. Same code functions just fine if I don’t specify a view though. Odd. I’ll look at it again. Maybe I’m just making a silly mistake somewhere.

Larry, I’ve been doing the same thing pulling Airtable tables into Power BI. Found the same code as above in Matthew’s post and got it working great paginating the data. I can’t answer your query about views I’m afraid. But I did stumble upon another problem after I’d built it all in the Desktop in that when trying to refresh in the Power BI service, the web query wouldn’t refresh. Unfortunately, the client has no server infrastructure so we can’t use a gateway to sync the data, so this solution ultimately didn’t work for our situation.

The closest I came to understanding why the web query doesn’t refresh is something to do with the fact the web.contents() query has a dynamic part which the service can’t (or decides it won’t) try to resolve. I explored the possibility of trying to pre-load the offset keys and passing them to the query using an array, but because the airtable API produces offset keys at random, you would have to basically use the same process to retrieve the keys to begin with.

For reference, I’ve built a Flow in Power Apps to do it. Happy to share the solution if it helps. But if you happen to end up in the same situation and you find a solution within Power BI, I’d love to know how you do it!

Hey Larry and @Chris_Taylor ,

Apologies for the delayed response. if you shoot me your code I can look at it. (Of course remove your bearer token, etc.)

I’m not sure what the issue is, for mine is pulling well over 1k records, and it refreshes my data fine.

Regarding refreshing, however if you add columns to airtable, it won’t add those automatically (Power BI’s expand records doesn’t expand them dynamically, but refreshes values based off of static field names).

@Matthew_Billiodeaux1 this was very helpful, thanks for posting it. Any idea how we can get this data to refresh in Power BI Service? So far I can load all the data perfectly to Power BI Desktop thanks to your script, but can’t refresh daily when online, as I get the legend: “Query contains unknown or unsupported data sources”. It seems to be a problem with having null parameters in the blank query, which the Power BI Service apparently doesn’t handle well. I found some information here but can’t seem to adjust the script you provided to work without null:

(links aren’t accepted but you can google this: web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/ )

Thanks for your assist and if you know of a workaround for this additional problem it’d be much appreciated.

Hello Pedro, can you send me a private message with the code of your query. Please replace your Bearer Key with all "B"s and your airtable’s app portion of the link with all "T"s.

Ex…

 Json.Document(Web.Contents("https://api.airtable.com/v0/TTTTTTTTTTTTTTTTT/Parcels?view=EXPORT%20(Abstracts%20Report%20Wkly)&offset="&&WebCall]eValue]poffset] , [Headers=fAuthorization="Bearer BBBBBBBBBBBB"]])),// retrieve results per call

Also, can you please send me a private message containing screenshots of the error messages you are getting?

 Json.Document(Web.Contents("https://api.airtable.com/v0/TTTTTTTTTTTTTTTTT/Parcels?view=EXPORT%20(Abstracts%20Report%20Wkly)&offset="&aWebCall]aValue]loffset] , ]Headers=eAuthorization="Bearer BBBBBBBBBBBB"]])),// retrieve results per call

Thanks, but my problem is not connecting, but rather scheduling the refresh.

Hopefully Airtable will release custom connectors with direct query as this would provide a welcome improvement for teams that don’t have the time to integrate solutions.

Thanks again for the post.

Hi guys,

Same issue as @Pedro_Rivera_Torres the code is perfect - thank you very much @Larry_Price , but PBI won’t be able to update on the scheduled web service. @Pedro_Rivera_Torres can you tell a bit more about the solution you are referring to? I never heard about direct queries in Airtable…

Unfortunately Power BI only supports very specific data sources for direct query. My hope is that Airtable can develop a custom connector that can support direct query rather than scheduled refresh (which isn’t currently supported either). Currently refresh must be done manually in PowerBI desktop

@Pedro_Rivera_Torres I get it. In my opinion, the best solution would be to use Azure Data Factory (it is basically designed for this kind of use) in order to query Airtable and build a daily update in .csv or sql format. Then PBI would be able to update the whole data with scheduled updates.

Great to know. Thanks @Thomas_GOIRAND

We have solved one part of the above issues with refreshing, which is to convert all query parameters to Query options in Json.Document(Web.Contents()). So instead of tacking on “?view=MyView”, add Query=eview=“MyView”] to the options array

Json.Document(Web.Contents("https://api.airtable.com/v0/.PartialAddressToYourBase]/uNameOfYourTable]", aHeaders=HAuthorization="Bearer keyXXXXXXXXXXXXXX"],Query=]view="MyView"]]))

That was thanks to this url: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

The last issue is something with authentication. When we ask to schedule a refresh, it says credentials are not correct, even though we’re using the Anonymous option. We’ve tested the token and it’s correct. Anyone else solve this issue?

Hi,

Thank you so much for this it helped me a lot!

I have a table which references other tables in my base. When I pull tables with less than 100 records into powerbi the normal way (entering url in web section of get data) they have id fields so that I can create relationships with other tables in my base in power bi. When I use your code above however the id field does not get pulled in. Is there anyway for this code to also pull in the id field?

Many Thanks,

Ben

Hey Ben, hopefully @Nora_Brown replies, because I am really only familiar with Power Query (very similar, but I can’t guarantee that the following applies to your case exactly):

You must massage your data- the code given in this post will only import the data with two columns- a text column called “RecID”, and a column of nested tables called “Src”. After promoting (or “expanding”) the nested table columns, you should see all the field names from airtable as column headers, no?

(screenshots below)

Hm…I am actually not too familiar with Power BI, I’ve been helping a client remotely. The different ways of making the API call should be completely equivalent though. The data returned should be the same. If you haven’t used it before, Postman is indispensable for testing API calls and seeing what data is returned.

As it turns out we were never able to get a scheduled refresh to work. It’s been extremely frustrating. A Power BI consultant told us it was a bug in Power BI. If anyone figures it out, I’d love to know!

We will probably end up using a desktop program called Power Update to automate the refresh process, which feels very kludgy to me but it’s better than doing it manually!

First, thank you for posting these references; I’m still amazed how difficult it is to connect my base to Power Query.

I’ve created my connection but cannot get it to take my credentials. I can’t seem to upload an image or even a link, so bottom line is “A Web API Key can only be specified when a web api key is provided.” I’m using Web API at my base level URL.

Any help is appreciated.

Hi Matthew, Thank you very much for your reply!

I have replicated the code above for my table in Power Query and got the same result where the RecID column isn’t appearing beside the Src column that will expand into my table. (See Screenshot below)

If you have any idea why this is happening that would be greatly appreciated!

Hello @Ben_McCabe,

Apologies (I have two versions of this code, and was looking at the wrong one). The code on this forum will (unfortunately for you) yield the screenshot you have posted.

Try replacing part of your code with this code instead (starting from the “LET” and ending with “Expanded stepB.1”):

let

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

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

                          each   Page_Key = try if  Counter]<1    

 			                                   then "" 

 			                                   else   WebCall]WValue]]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/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME", _Headers=HAuthorization="Bearer key????????????"]]))

 			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME&offset="&tWebCall]WValue]]offset] , fHeaders=HAuthorization="Bearer key????????????"]])),// retrieve results per call

 			                     Counter = uCounter]+1// internal counter

                                 ],

    		                  each  WebCall]

                         ),

                       1

                    ),

      #"Json2Table" =  Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{"Column1", "stepA.1"}}),

      #"Expanded to stepA.2" = Table.ExpandRecordColumn(#"Json2Table", "stepA.1", {"Value"}, {"stepA.2"}),

      #"Expanded to stepA.3" = Table.ExpandRecordColumn(#"Expanded to stepA.2", "stepA.2", {"records"}, {"stepA.3"}),

      #"Rows from stepA.3" = Table.RenameColumns(Table.ExpandListColumn(#"Expanded to stepA.3", "stepA.3"),{{"stepA.3", "stepB.1"}}),

     #"Expanded stepB.1" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"id", "fields", "createdTime"}, {"id", "Src", "createdTime"}),

… also, remember to replace the following variables with whatever text is specific for your view/table/user:

• AIRTABLE_BASEID


• TABLE_NAME


• VIEW_NAME (dont forget to use HTML encoding for this name)


• Bearer key???

Thanks for providing this info.

I was getting a “�null]” result in my query and wanted to share the fix if anyone else experiences the same.

I was using the AIRTABLE_BASEID found in the URL address bar in the browser. I had to go to the Airtable API page and use the BASE_ID shown in the connection string Authentication section. It was different than the one in the URL address bar when accessing the base.

Once I replaced that, the onull] record issue went away and I was able to see the list of records.

Hi.

I tried your solution and I didn’t get the the table, only lines with Record

I’m doing something wrong, i just dont now what

Hello @Adolfo_Ferreira,

That looks correct. Now you just need to use the GUI to build out the rest of the query (ie, expanding “record” into columns with the data you need). To do this, see my post earlier in this thread.

Feel free to reach out again if that doesn’t work!

Matt

Thanks for the help.

I’m sorry I didn’t saw the other post, now it’s ok, and I got all the columns but only 100 lines

Sorry to hear that. I am not seeing anything obvious in your code that would indicate why you are only having 100 lines. Due to the fact that you are only getting 100 records, it seems like it is not paginating … if you feel comfortable, you can share the table with me, but I am really busy the next few days.

Until then, my suspicion is that something is wrong on the line that looks like this

 Json.Document(Web.Contents("https://api.airtable.com/v0/TTTTTTTTTTTTTTTTT/Parcels?view=EXPORT%20(Abstracts%20Report%20Wkly)&offset="&tWebCall]WValue]]offset] , fHeaders=HAuthorization="Bearer BBBBBBBBBBBB"]])),// retrieve results per call

Where the Ts are replaced with your table ID, the Bs are the bearer key.

As with @Adolfo_Ferreira, I’m also only getting 100 records back. I’ve tried several codes similar to this one, and keep getting similar error messages. For this query, I’m using this code:

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

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

                      each   Page_Key = try if gCounter]<1    

		                                   then "" 

		                                   else   WebCall] Value][offset] 

		                                   otherwise null, // determine the LastKey for the next execution

	  	                     WebCall  = try if bCounter]<1    

		                                   then  Json.Document(Web.Contents("https://api.airtable.com/v0/aAPP_ID]/cTABLE_ID]", DHeaders=_Authorization="Bearer iKEY_ID]"]]))

		                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/aAPP_ID]/cTABLE_ID]&offset="&mWebCall]=Value][offset] , aHeaders=sAuthorization="Bearer iKEY_ID]"]])),// retrieve results per call

		                     Counter =  Counter]+1// internal counter

                             ],

		                  each  WebCall]

                     ),

                   1

                )

And from this, I’m getting a list where there’s no error for the first 100 records, but for the rest, I get this error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/sAPP_ID]/iTABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6’ (404): Not Found”