Visualizing Airtable records in Microsoft Power BI & Power Query
  • 16 Nov 2022
  • 5 Minutes to read
  • Dark
    Light

Visualizing Airtable records in Microsoft Power BI & Power Query

  • Dark
    Light

Note
While these articles are meant to serve as helpful guides for our customers, one on one support for integrations with 3rd party tools is limited in support interactions.

This article outlines a solution to efficiently and automatically bring information from your Airtable base into Microsoft Power BI and Microsoft Power Query in Excel.


What is Power BI and Power Query and how can I use it with Airtable?

Power Query is Microsoft’s data connection tool that enables users to connect, combine, and refine data sources. These connections can come from self-hosted and/or static files like XLSX, CSVs, XML files, as well as cloud-based sources such as Azure SQL Databases and Salesforce.

Currently, Power Query is only available in the Windows versions of Excel and Power BI. Using Power Query, we can pull records from Airtable into Excel or Power BI so that teams can create complex dashboards, and enrich Airtable data with other data sources available in Power BI and Microsoft Excel.


What will I need?

  • A computer running Windows
  • Power BI Desktop or Microsoft Excel 2016 (or later)
  • An Airtable base ID, table ID, and your API key
  • The below query in Microsoft's M formula language


Step-by-step instructions

The process for connecting Airtable to Microsoft Power BI and Excel (Power Query) are very similar. In this section, we'll cover how to use the Airtable API to connect to both.

1. Copy Template M Query

We've developed the following template M Query that should help you get started bringing data from a Table in an Airtable base to a Power BI Query. This query uses Query Parameters to define your Airtable BASE_ID, TABLE_ID, and API_KEY as variables. After we set the parameters in the next steps, you should not need to modify the query directly.

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"
2. Create a Query

Create a new Power BI document and select Get Data from the Home toolbar. In the dropdown menu, select Blank Query to open the Power Query Editor.

4403481983383Image2021-06-30at103847AM.png

3. Create Parameters

In the toolbar of the Power Query Editor, select Manage Parameters. This will open the window where you can create and edit Power Query Parameters.

4403483079575Capture.PNG

This is where you will create and define the parameters referenced in the template M Query. Power BI's Query Parameters allow users to define variables to be used in a query. For each parameter, we're going to set a Name, Type, and Current Value. All of the parameter types will be set as text, and we'll set the Name and Current Value using the keys/ids identified below.

API_KEYBASE_IDTABLE_ID

Your API key.

The ID of the base you want to query.

 The ID of the table you want to query

You can find your API key from your Airtable account page here.

You can find your Base ID by navigating to the API documentation for your base.

You can find your table ID by navigating to a view of your table, and looking in your URL/address bar.

Your API key will start with the letters key

Your Base ID will start with the letters app

Your Table ID will start with the letters tbl

4. Paste In Query

After creating your three parameters and selecting OK from the Manage Parameters window, the sidebar of your Power Query Editor should list the three variables you just set.

4403482450583unnamed.png

Once you have your parameters, select Query 1 from your sidebar, and then select Advanced Editor in the toolbar. Remove any existing query and paste the M Query into the editor. Press Done.

4403483908119Capture1.PNG

You should now see a list of records from your Airtable base’s table. From here you can transform and shape the data in your table. Once you've finished manipulating your data, select Close & Apply in the top left corner of the Query Editor. Now you can try making changes in Airtable and refreshing the query in Excel, you should see your updates reflected.

4403481907863Capture2.PNG

NOTE

PowerBI will only load a field if the first record coming from Airtable has data in that field. So if the field is empty, then it won’t show up in Power BI.

Authentication

You may be asked to provide Authentication for https://api.airtable.com. In this menu, you should select “Anonymous” for the Authentication mechanism. You can select the Privacy Level that’s best for your use case, and then click “Sign In.” We are already including the API key in the actual query using the API_KEY parameter, so there is no extra information to provide here.

Transforming and shaping your data

Some field types including single selects, multiple selects, linked records, and collaborator fields will show “List” or “Record” instead of the values you may be used to seeing in the Airtable grid view. This is consistent with how the Airtable Web API returns information. Depending on your use case and needs, you may want to use Power BI’s “Expand to New Rows” or “Extract Values…” functionality that Power BI outlines in their support documentation.

Automating refreshes of your Power BI report

To keep your data up to date, Power BI allows you to schedule a refresh of a report once it's been published in your organization’s workspace. Microsoft outlines how to set up a scheduled refresh in their support documentation.

Connecting Airtable to Microsoft Excel Power Query

The process here is exactly the same as in Power BI. To get started, open a new workbook, and then click on Data, Get Data, From Other Source, and Blank Query. The rest of the steps are the same as above.


What's next?

There are many different ways to go about integrating Power BI, Excel, and Airtable! We would love to see how you and your team approach this integration and what specifically you’re trying to leverage Airtable for.

Once you have the initial integration in place, you can begin to build dashboards and reports on top of this data. Some teams choose to pull data from multiple bases into Power BI to generate reports that provide insight into related data across their workflows.

Feel free to post your integration with other Airtable creators on our Community Page.


Was this article helpful?