Help

Re: Airtable link to Excel via Power Query

9376 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Billio1
7 - App Architect
7 - App Architect

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

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

36 Replies 36

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:

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

Pedro_Rivera_T1
4 - Data Explorer
4 - Data Explorer

Great to know. Thanks @Thomas_GOIRAND

Nora_Brown
6 - Interface Innovator
6 - Interface Innovator

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?

Ben_McCabe
4 - Data Explorer
4 - Data Explorer

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

Matthew_Billio1
7 - App Architect
7 - App Architect

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)

  • image.png
  • image
  • image.png
  • image.png
Nora_Brown
6 - Interface Innovator
6 - Interface Innovator

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!

RGordon013
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

Matthew_Billio1
7 - App Architect
7 - App Architect

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][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/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME", [Headers=[Authorization="Bearer key????????????"]]))
 			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// 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"}}),
     #"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???