Help

Re: Airtable link to Excel via Power Query

2999 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
Simon_Lipsky
4 - Data Explorer
4 - Data Explorer

Hello, This information has been very helpful for linking airtable to Excel, I was able to get it to work properly, unfortunately some of the record names are strings generated by Airtable for the linked record fields instead of the name displayed in airtable or displayed when you export a csv of the table. Is there a way to translate these generated record strings into the names that are displayed in airtable?

Thanks

Hello Simon, please send a screen shot. If I understand you correctly, your solution is to create another field to extract the data from your linked record field in a more friendly fashion.

EX:
Lets assume you have an INVOICES_tbl (that shows the total amount owed to each client per invoice) and a CLIENTS_tbl (with contact info of each client, and a field for the clients name called Full_Name).

  1. Lets assume your linked field is called CLIENT_Link and it…
    • links to data in your CLIENTS_tbl.
    • and dislplays the name, but returns the record it in PoweQuery / Power BI
  2. So, to return the client’s NAME instead of the record ID, create a field called ClientName_Text like so…
    • image

Yep, this solved the problem, thank you very much for you hasty response.

you may be having the same issue other’s have had.

Perrin_Romney
5 - Automation Enthusiast
5 - Automation Enthusiast

Full disclosure, I’m a noob to power query and Airtable. This gives me the fields but not the ids. All linked fields use ids, which I need to then reference. How do I add a column with the id in it so I can look up all linked fields?

I can get a list of JUST the ids if i change {“fields”} out for {“id”} on the #“Source” line. I want to be able to do both, but I don’t know how to make it multiple columns.