Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Personal Access Token Power BI Excel M Query Correction

Topic Labels: API
1457 2
cancel
Showing results for 
Search instead for 
Did you mean: 
aricshow
4 - Data Explorer
4 - Data Explorer

Edit:

This is no longer needed, AirTable has updated the official script with a superior correction.

https://support.airtable.com/docs/visualizing-airtable-records-in-microsoft-power-bi-power-query

Original Post:
Here is a version of the M Query code for Power BI/Excel with a small correction that resolves the issue of the query endlessly pulling the first page of results:

 

 

 

let Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0],
	 each [Last_Key] <> null,
	 each [
	   Last_Key = try if [Counter]<1 then ""
	   else
	     [WebCall][Value][offset] otherwise null,
	   WebCall = try if [Counter]<1
	   then
	   Json.Document(Web.Contents("https://api.airtable.com",
	     [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"", Headers=[#"Authorization"="Bearer "&PERSONAL_ACCESS_TOKEN,#"Content-Type"="application/json"]]))
	   else
	   Json.Document(Web.Contents("https://api.airtable.com",
	     [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?offset="&Last_Key&"", Headers=[#"Authorization"="Bearer "&PERSONAL_ACCESS_TOKEN,#"Content-Type"="application/json"]])),
	   Counter = [Counter]+1
	 ],
	 each [WebCall]
	),1),
	 #"Converted to Table" = Table.FromList(
	   Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	 #"Expanded Column1" = Table.ExpandRecordColumn(
	   #"Converted to Table", "Column1", {"Value"}, {"Column1.Value"}),
	 #"Expanded Column1.Value" = Table.ExpandRecordColumn(
	   #"Expanded Column1", "Column1.Value", {"records"}, {"Column1.Value.records"}),
	 #"Expanded Column1.Value.records" = Table.ExpandListColumn(
	   #"Expanded Column1.Value", "Column1.Value.records"),
	 #"Expanded Column1.Value.records1" = Table.ExpandRecordColumn(
	   #"Expanded Column1.Value.records", "Column1.Value.records",
	   {"id", "fields", "createdTime"},
	   {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
	 #"Renamed Columns" = Table.RenameColumns(
	   #"Expanded Column1.Value.records1",{{"Column1.Value.records.id", "_airtableRecordId"},
	   {"Column1.Value.records.createdTime", "_airtableRecordCreatedAt"},
	   {"Column1.Value.records.fields", "_airtableRecordFields"}}),
	 #"Reordered Columns" = Table.ReorderColumns(
	   #"Renamed Columns",
	   {"_airtableRecordId", "_airtableRecordCreatedAt", "_airtableRecordFields"}),
	 #"Expanded Record Fields" = Table.ExpandRecordColumn(
	   #"Reordered Columns", "_airtableRecordFields",
	   Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]),
	   Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]))
	in
	 #"Expanded Record Fields"

 

 

 

 

2 Replies 2
mtkomo
5 - Automation Enthusiast
5 - Automation Enthusiast

Trying to pull AirTable data into PowerQuery and Excel. Suggested anonymous connection doesn't work. Getting authentication error. Any idea?

mtkomo
5 - Automation Enthusiast
5 - Automation Enthusiast

curl "https://api.airtable.com/v0/appMYAPP/tblMYTABLE" \
-H "Authorization: Bearer patMYTOKEN"


{"error":{"type":"AUTHENTICATION_REQUIRED","message":"Authentication required"}}