Airtable link to Excel via Power Query


#1

Here is a way to automatically pull information from airtable into Excel via power-Query using a bit of coding.

If you are looking to just copy and paste the code you need without much explanation, ignore the details :slight_smile:

Details

SECTION 1- CONNECTING EXCEL TO POWERQUERY-

The first issue a user may run into while trying to get data to automatically feed into Excel is creating an authenticated connection to Airtable from Excel’s powerQuery. The following code will be used in a later step, but it is given here as a preview of the code that is chiefly responsible for the data connection to airtable’s api.

Source = Json.Document(Web.Contents(“https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))

You will be replacing “[PartialAddressToYourBase]”, “[NameOfYourTable]”, and the “keyXXXXXXXXXXXXXX” with your particular information at a later step.

Now even if a query were created with its sole source of data being the above code, the output would be a very useless and unfriendly format; additionally, airtable’s api only allows users to get data via paginated requests… so unless the query looped through all the pages of data, the resulting datasheet in excel would only contain the first few hundred records of data.

SECTION 2- PAGINATING THROUGH AIRTABLE’s DATA

In order to pull ALL of the Base’s data into Excel, the query needs to make calls to Airtables api repeatedly until it reaches the end of the records. So, instead of just setting the query source as using the above code, a query should instead loop through all of the records in the Airtable Base as follows:

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][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/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]]))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]])),// retrieve results per call
			                     Counter = [Counter]+1// internal counter
                                ],
   		                  each [WebCall]
                        ),
                      1
                   )

SECTION 3- FORMATTING THE DATA

Again, as stated at the beginning, the above code will return data in a pretty nasty format. So, the rest of the code is necessary to reformat it in a record by record fashion:

#"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"}}),
#"Source Records" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"}),

Now you have an idea of what the code is doing, proceed to put it all together in a how-to-fashion.

STEP 1.
Copy and paste into Excel’s advanced power query editor the following code.

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][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/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]]))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]])),// retrieve results per call
			                     Counter = [Counter]+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"}}),
     #"Source" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"})
in
     #"Source"

STEP 2
Replace the following strings with your particular information:

  1. [PartialAddressToYourBase]

  2. "[NameOfYourTable]"

  3. "keyXXXXXXXXXXXXXX"

For example,

Json.Document(Web.Contents(“https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))

Becomes

Json.Document(Web.Contents(“https://api.airtable.com/v0/tb2rvf9QCSxi2QJJ3/Books”, [Headers=[Authorization=“Bearer keyDB8lDEPgMun”]]))

STEP 3
After copying, pasting, and replacing hit “done” in excels Advanced Query Editor. Upon attempting to run the query, a yellow bar will appear at the top of the Power Query application. Click “authenticate” or something like that. A dialog box will pop up asking to authenticate the query. I used regular web authentication at Airtable’s Base level (ex www.airtable.com/v0/tbl21OZVeYPLuPQl4 instead of www.airtable.com).

FINAL STEPS
This part is up to you. Anyone familiar with power query will then be able to run with this process from this point forward.

CREDITS

Apologies for not remembering those who helped me (although I visited many websites, read the airtable api, and had to dig into the powerquery help to figure this out, there was one website that especially helped and I will try to find it and update this post later).


Airtble Vs Power BI
Help needed to connect Airtable to Power BI / Excel
#2

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.


#3

Hey Larry,

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

?view=[URLencodedNameOfYourView]

So, the entire line will look something like this:

Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?view=[URLencodedNameOfYourView]", [Headers=[Authorization="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/[PartialAddressToYourBase]/[NameOfYourTable]?view=[URLencodedNameOfYourView]”)

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

 WebCall  = try if [Counter]<1    
	               then Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?view=[URLencodedNameOfYourView]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXX]]))
                   else Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?view=[URLencodedNameOfYourView]&offset="&[WebCall][Value][offset], [Headers=[Authorization="Bearer keyXXXXXXXXXXXXX"]]))

Matt


#4

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.


#5

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!


#6

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).


#7

@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.


#8

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][Value][offset] , [Headers=[Authorization="Bearer BBBBBBBBBBBB"]])),// retrieve results per call

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


#9

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.


#10

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…


#11

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 :frowning:


#12

@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.


#13

Great to know. Thanks @Thomas_GOIRAND


#14

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=[view=“MyView”] to the options array

Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="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?