Hi All,
I am new to connecting AirTable to Power Query using API. I’m able to create a query by using the following parameters:
API_KEY
BASE_ID
TABLE_ID
And below is what I use for the query (from AirTable site). This will only pull in one table at a time.
How do I pull in multiple tables at a time?
Also, I have a column that is a calculation that is pulling in as “list”. Is there a way for it to only pull in as a value or do all associated tables need to be pulled-in?
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&"?api_key="&API_KEY&""]))
else
Json.Document(Web.Contents(“https://api.airtable.com”,
[RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?api_key="&API_KEY&"&offset="&Last_Key&""])),
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”
