Help

Re: How to Import Table in to Power BI? - Post API_KEY Depreciation

534 0
cancel
Showing results for 
Search instead for 
Did you mean: 
bvbull200
4 - Data Explorer
4 - Data Explorer

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"

 

1 Reply 1

Hmm, you're using the `api_key` param in your call and that's been deprecated I'm afraid.  You're going to need to use a personal access token instead of an API key, and Airtable has a guide on use them here:
https://airtable.com/developers/web/guides/personal-access-tokens

You'll then need to modify your code so that you do a GET request for the data instead, and include the token in the header