How to: Use Excel Power Query to Retrieve Data from Airtable

When you have your data neatly set up on Airtable, especially when they are particularly financial data and numbers, you would want to connect to them on Excel (or Power BI), and do the analysis (or charts) from the latter.

Excel’s Power Query can ETL (Extract, Transform, and Load) data from your Airtable database, and here’s how to even work around the 100 Rows Limit or Pagination.

What you need are:

  1. Base ID
  2. Table  ID
  3. API Key

So I created a throwaway Airtable account for the API key, a throwaway workspace from that account, and and loaded the Simple Project Tracker base template, just for this demonstration.

You can get your API key from your account page (https://airtable.com/account):

You can get your Base ID and Table ID by going to the base and table you want to connect to and copy them from the URL:

Combine them with the code below:

let
    Basename = "app2Q6godlUKbK9VH", //Change this to your base ID
    Tablename = "tblrMj6G8PtXYqAyH", //Change this to your table ID
    APIKey = "keytnTS8KVSqOUBpU", //Change this to your API Key
    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/" & Basename & "/" & Tablename, [Headers=[Authorization="Bearer "& APIKey]]))
            else  Json.Document(Web.Contents("https://api.airtable.com/v0/" & Basename & "/" & Tablename & "?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer "& APIKey]])),// 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", {"Priority", "Stage", "Name", "Project Photos", "Notes", "Deadline", "Tasks"}, {"Priority", "Stage", "Name", "Project Photos", "Notes", "Deadline", "Tasks"})
in
    #"Expanded Src"

Create a blank query on Excel’s Power Query, click the Advanced Editor, and paste the M Code above like so:

By then, you can load your data as a table in Excel, or even add the table to the data model for further processing by Power Pivot:

API Key Security

I’m sure you plan to share the Excel file you just created that retrieve the data from your Airtable base with just a click of a button with your co-workers, but you’re worried that you’re also sharing your own API key embedded in that Excel file.

Well, just like me, you can also create a throwaway account, add that account to the base as read-only, then connect Power Query to that throwaway account instead. That way, if bad guys get a hold of your API key, they got an API key that doesn’t have write privileges anyway. They can’t mess up with your data.

Leave a Reply

Your email address will not be published.