Airtable lets you store information in tables, which are a part of bases and workspaces. Over time, this database can expand significantly, making it harder to track and analyze data within the platform. That’s where you can use business intelligence and data visualization tools like Power BI.
In this article, we cover the different ways to connect Airtable to Power BI, from using no-code solutions to integrating the two apps via spreadsheets.
Options to send Airtable data to Power BI
In an Airtable base, you can download any table as a CSV file, which you can manually import into Power BI.
But if you want to export data automatically, there are three options:
- Data connector by Coupler.io – This no-code reporting automation platform connects 60+ popular apps to spreadsheets, BI tools, and data warehouses. You can use it to export, transform, and analyze Airtable data in Power BI in a few simple steps. The Power BI connector by Coupler.io also supports automatic data refreshes that keep the reports up to date. You don’t need to have any tech skills or prior knowledge to use it.
- Call the Airtable API in Power Query – This method uses the Airtable API directly in Power BI. You’ll need to write a Power Query script for each table you want to export. Therefore, this method is suitable for users who are familiar with APIs and are comfortable with coding.
- Indirect Connection via Google Sheets – You can use the Airtable Automations feature to export data on specific triggers to Google Sheets. Then, connect the sheet to Power BI as a data source. This method is relatively simple, but it can get cumbersome if you have multiple tables to export.
In the following sections, we cover each of these options in detail.
Method 1: Connect Airtable to Power BI using Coupler.io
1. Connect to Airtable
Click the Proceed button in the widget below. It’s preset for an Airtable to Power BI export.
You’ll be redirected to the Coupler.io login page, where you can sign up and start using the tool for free without any credit card.
Then, open the Airtable view you want to export and click Share and sync in the top panel to get its link.
Paste the URL into the importer, and click Finish and Proceed.
Optionally, if the view is private, you can provide its password.
Repeat these steps if you want to add several Airtable data views. You will need to connect each view as a separate importer. Alternatively, you can include other views into the same importer as an additional data source (press +Connect one more source). In this case, you will be able to combine data from different views in one dataset. Once done, proceed to data transformation.
2. Organize data
The importer will display a preview of the data. Here, you can transform the data and make it analysis-ready. For example, you can:
- Hide unwanted columns
- Filter or sort data
- Add new columns using formulas
- Merge data from multiple Airtable views
Once you’re satisfied with the transformations, continue to the next step.
3. Load data to Power BI and schedule refreshes
Follow the onscreen instructions to generate an integration URL and copy it. You’ll need it to import data into Power BI. You can actually use this URL to access data in JSON format. At the same time, Coupler.io lets you export data from Airtable to JSON as a separate destination.
Now, set up a schedule and choose how often and when to auto-refresh data by transferring the latest changes from Airtable to Power BI. For example, you can set it to “every 15 minutes” to sync the platforms in almost real-time. Then, save and run the importer.
Open Power BI Desktop, go to Get Data > From Web, and use the copied integration URL.
The Airtable data will be loaded into your report. Now you can start adding visuals.
Coupler.io will refresh data automatically as scheduled so that reports always show the latest metrics. This is the easiest method to set up an Airtable Power BI integration and build self-updating dashboards without scripting.
You can also use Coupler.io to easily export your Airtable data to other destinations, such as Airtable to Excel, Looker Studio, and BigQuery, for further analysis.
Method 2: Use Power Query to get Airtable data in Power BI
You can create and run a Power Query script to call the Airtable API. For this, you’ll need the API key and other details from Airtable to prepare the query. Here’s the step-by-step guide to transferring Airtable data using Power Query:
Get the Access Token, Base ID and Table ID
The Airtable personal access token is required for API call authentication. You’ll also need the base ID and table ID for the dataset to export.
Here are the steps to generate a personal access token:
- Go to airtable.com/create/tokens and click Create token.
- Now, enter a name for the access token, e.g., “Integration with Power BI”. Under the Scopes, add data.records:read to allow it to read the data and grant access to the workspace/base you’re working with. Click the Create token button.
- The personal access token (API key) will be displayed. Copy and save it.
Open Airtable and navigate to the data you want to export. Look at your address bar — it will contain both base ID (starts with app) and table ID (starts with tbl), as shown below.
Prepare a query script
Copy and paste the following template script into Notepad or any other text editor.
let
BASE_ID = "Insert your base ID here",
TABLE_ID = "Insert your table ID here",
PERSONAL_ACCESS_TOKEN = "Insert your personal access token here",
Pagination = List.Skip(
List.Generate(
() => [Page_Key = "init", Counter=0],
each [Page_Key] <> null,
each [
Page_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,
Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]]))
else Json.Document(Web.Contents("https://api.airtable.com",
[RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?offset="&[WebCall][Value][offset],
Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]])),
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"
In the above script, replace the placeholders with your Airtable personal access token, base ID, and table ID.
Run query in Power BI
Open the Power BI report and navigate to Get Data > Blank Query.
In the top menu, click the Advanced Editor option. Paste the query you prepared in the previous step and click Done.
The Power Query Editor will display a preview of Airtable records. Here, you can transform the data if required. Finally, click Close & Apply to connect the data source.
Similarly, you can export other tables and views by modifying the base ID and table ID values in the query. However, you’ll need to create a separate query (data source) for each table, which can get challenging to manage over time.
As you can see, this option to export data from Airtable can be somewhat complicated.
Method 3: Indirect connection via Google Sheets
Using Airtable Automations, you can export data to Google Sheets based on specific events or a schedule. Then, you can use this sheet as a Power BI data source for building reports.
In the Airtable base, select Automations in the top menu bar and create a new trigger by clicking the +Add trigger button.
Here, you can choose from various event-based and time-based triggers. For example, we have selected the When record update trigger that will fire when there is any change in the Airtable tables.
Add a new action to the workflow by selecting Add advanced logic or action > Google Sheets > Append row.
Connect your Google account and select the spreadsheet and sheet for the data export. It should contain column headers that we’ll use as a reference.
In Row Data, map the Airtable fields one by one with sheet header names.
After that, test the connection and see the results in the Google Sheets. If everything looks good, enable the automation.
Open the Power BI report to which you want to export Airtable data. Go to Get data > More > Google Sheets and enter the URL of the Google Sheets connected with Airtable.
Sign into your Google account and authorize access to Power BI. Once connected, the Google Sheets dataset will connect to Power BI.
What’s the best option for Airtable Power BI integration?
For technical users, Power Query may sound like a good option. But remember, you’ll need to write a script for every table you want to export. This process is prone to human errors and could also affect the reporting performance. You can use Google Sheets to export a few columns, but it’s not efficient when working with large datasets.
However, the integration does not need to be that complicated. Coupler.io offers an easier and more convenient way to connect Airtable to Power BI. It also supports data transformation and automation, which helps you get fresh and analysis-ready data from Airtable. If you need an even simpler way of visualizing your Airtable data, check out our guide on how to connect Airtable to Tableau.
In any case, sign up for Coupler.io now and start analyzing data from 60+ apps.
