I had been importing an Airtable in to Power BI, only needing it every few months, but now am unable to refresh. It appears that API_KEYS have been depreciated as of February and I'm unable to find a resource for how to reestablish the connection.
Does anyone have reliable M code I can drop in the advanced editor to get my tables back?
I appreciate the assistance.
For what it is worth, this is the entirety of the existing M that I had been using. It now says that it can't authenticate with current credentials. I have set it to anonymous, but to no avail.
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", {"Name", "Batch", "Finish", "Palate", "Heat", "ID", "Nose", "Entered By", "Booker's Profile", "Notes"}, {"_airtableRecordFields.Name", "_airtableRecordFields.Bourbon", "_airtableRecordFields.Finish", "_airtableRecordFields.Palate", "_airtableRecordFields.Heat", "_airtableRecordFields.ID", "_airtableRecordFields.Nose", "_airtableRecordFields.Entered By", "_airtableRecordFields.Booker's Profile", "_airtableRecordFields.Notes"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Record Fields",{{"_airtableRecordFields.Name", "Name"}, {"_airtableRecordFields.Finish", "Finish"}, {"_airtableRecordFields.Palate", "Palate"}, {"_airtableRecordFields.ID", "ID"}, {"_airtableRecordFields.Nose", "Nose"}, {"_airtableRecordFields.Entered By", "Entered By"}, {"_airtableRecordFields.Bourbon", "Bourbon"}, {"_airtableRecordFields.Notes", "Notes"}, {"_airtableRecordFields.Heat", "Heat"}, {"_airtableRecordFields.Booker's Profile", "Booker's Profile"}}),
#"Expanded Name" = Table.ExpandListColumn(#"Renamed Columns1", "Name"),
#"Expanded Bourbon" = Table.ExpandListColumn(#"Expanded Name", "Bourbon"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Bourbon",{{"Finish", Int64.Type}, {"Palate", Int64.Type}, {"Nose", Int64.Type}, {"Booker's Profile", Int64.Type}}),
#"Expanded Entered By" = Table.ExpandRecordColumn(#"Changed Type", "Entered By", {"name"}, {"Entered By.name"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Entered By",{{"Entered By.name", "Entered by"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "_airtableRecordId", "_airtableRecordCreatedAt", "Name", "Bourbon", "Finish", "Palate", "Nose", "Entered by"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"Bourbon"}, #"Bourbon List", {"Batch Key"}, "Bourbon List", JoinKind.LeftOuter),
#"Expanded Bourbon List" = Table.ExpandTableColumn(#"Merged Queries", "Bourbon List", {"Batch Name"}, {"Bourbon List.Batch Name"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Bourbon List", {"Name"}, People, {"Reviewer Key"}, "People", JoinKind.LeftOuter),
#"Expanded People" = Table.ExpandTableColumn(#"Merged Queries1", "People", {"Reviewer"}, {"People.Reviewer"}),
#"Sorted Rows" = Table.Sort(#"Expanded People",{{"People.Reviewer", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Composite Score", each ((([Nose])*20)+(([Palate])*45)+(([Finish])*30)+(([Heat])*5))/10),
#"Renamed Columns3" = Table.RenameColumns(#"Added Custom",{{"Bourbon List.Batch Name", "Batch Name"}, {"People.Reviewer", "Reviewer"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Reviewer", type text}, {"Batch Name", type text}, {"Notes", type text}, {"Heat", Int64.Type}, {"Composite Score", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Notes With Reviewer and Batch", each [Notes] & " - " & [Reviewer] & " [" & [Batch Name] & "] - "),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Batch Name", "Batch Name - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Batch Name - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Batch Name - Copy.1", "Batch Name - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Batch Name - Copy.1", type text}, {"Batch Name - Copy.2", type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Changed Type2",{{"Batch Name - Copy.1", "Batch Code"}})
in
#"Renamed Columns4"
