Airtable link to Excel via Power Query

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 [null] record issue went away and I was able to see the list of records.

1 Like

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

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

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="&[WebCall][Value][offset] , [Headers=[Authorization="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( () => [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/[APP_ID]/[TABLE_ID]", [Headers=[Authorization="Bearer [KEY_ID]"]]))
		                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer [KEY_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/[APP_ID]/[TABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6’ (404): Not Found”

@Adolfo_Ferreira and @Ivan_Larson

I tried using Adolfo’s code, and it looks legit. I see no reason why it is throwing errors except for the fact that I can’t see your API table names. When it tries paginating it throws the following error:

"Web.Contents failed to get contents from 'https://api.airtable.com/v0/[...redacted for privacy...]/[...redacted...]&offset=[...redacted...]' (404): Not Found"

And this error renders the next page of results as “Null”.

So, please look at @Jon_Thomas1’s post. I think this is the issue you are facing.

I would test it for you with the code you shared, but do not have easy access to your API table names. I probably will be away from this discussion board for about a day, but will reply ASAP.

Hello @Adolfo_Ferreira and @Ivan_Larson ,

I am back from my “siesta”. After reviewing code that was shared with me, I found the error. If you are not using a particular view, then you had to modify the code, deleting the VIEW from the http request. When deleting the portion of the code that pertains to a parcticular airtable view, be careful NOT to delete the question mark (?) in the code that denotes HTTP request parameters…

This is easy to miss (as seen by the fact that I missed it when reviewing your code).

So, when NOT using a particular view for your import from airtable into excel, the following code should be modified FROM this

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

Into this format

...
 WebCall  = try if [Counter]<1    
			    then  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME", [Headers=[Authorization="Bearer key????????????"]]))
			    else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

So, in short we went from this code:

To this code:

Notice how we dropped the " ?view=VIEW_NAME " entirely from the first shown line, but on the
second shown line, we dropped only " view=VIEW_NAME " and kept the question mark (?)
but DROPPED THE AMPERSAND (&)

thanks @Matthew_Billiodeaux1 this worked like a charm; really appreciate saving me from re-inventing this :smile:

1 Like

Hi Guys,

Thanks for the original post and the replies. This helped me link AirTable data into MSExcel using PowerQuery. However the source table has 400 rows and only 100 are being downloaded into excel and it looks like a preview. Can one of you help me with any code updates.

Regards,

Bharat

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/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))
else Json.Document(Web.Contents(“https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines?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”}),
#“Expanded Src” = Table.ExpandRecordColumn(Source, “Src”, {“Developer / Researcher”, “Treatment vs. Vaccine”, “Product Category”, “Product Description”, “Stage of Development”, “Anticipated Next Steps”, “Clinical Trials for COVID-19”, “Funder”, “FDA-Approved Indications”, “Sources”, “Date Last Updated”, “CMS2”, “Record Update”, “Clinical Trials for Other Diseases (T only) / Related Use or Platform (V only)”, “Published Results”}, {“Src.Developer / Researcher”, “Src.Treatment vs. Vaccine”, “Src.Product Category”, “Src.Product Description”, “Src.Stage of Development”, “Src.Anticipated Next Steps”, “Src.Clinical Trials for COVID-19”, “Src.Funder”, “Src.FDA-Approved Indications”, “Src.Sources”, “Src.Date Last Updated”, “Src.CMS2”, “Src.Record Update”, “Src.Clinical Trials for Other Diseases (T only) / Related Use or Platform (V onl”, “Src.Published Results”}),
#“Filtered Rows” = Table.SelectRows(#“Expanded Src”, each true)
in
#“Filtered Rows”

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

1 Like

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…

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

1 Like

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

I suspect this is the error:

Contents(“https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines?offset=”&[WebCall][Value][offset]

Should be as follows:

Contents(“https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines&offset=”&[WebCall][Value][offset]