- Getting started with Airtable
- Introduction to Airtable basics
- Contacting Airtable Support Updated
- Airtable home screen
- Glossary of Airtable terminology
- Airtable technical requirements
- Feature differences between Airtable on desktop and mobile
- Airtable keyboard shortcuts
- Using Markdown in Airtable
- Adding descriptions in Airtable
- Finding Airtable IDs
- Airtable Automations
- Automations Overview
- Automation feature walkthroughs
- Integrated automation walkthroughs
- Airtable automation walkthroughs
- Linking existing records using automations
- Conditional groups of automation actions
- Repeating groups of Airtable automation actions
- Creating recurring records using automations
- How to delay Airtable automation runs
- Prevent automations from triggering by mistake
- Use automations to timestamp status updates
- Automation Triggers
- Airtable Triggers
- Airtable automation trigger: When record matches conditions
- Airtable automation trigger: When a form is submitted
- Airtable automation trigger: When record created
- Airtable automation trigger: When record updated
- Airtable automation trigger: When record enters view
- Airtable automation trigger: At scheduled time
- Airtable automation trigger: When webhook received
- Airtable automation trigger: When a button is clicked
- Airtable automation trigger: When email received Updated
- Integrated Triggers
- Airtable Triggers
- Automation Actions
- Airtable Actions
- Airtable automation action: Send email Updated
- Airtable automation action: Create record
- Airtable automation action: Update record
- Airtable automation action: Find records
- Airtable automation action: Sort list
- Airtable automation action: Run a script Updated
- Airtable automation action: Generate with AI
- Integrated Actions
- Airtable automation actions: Slack
- Airtable automation actions: Google Workspace
- Airtable automation action: Send MS Teams message
- Airtable automation actions: Outlook
- Airtable automation actions: Jira Cloud
- Airtable automation actions: Jira Server / Data Center
- Airtable automation actions: Salesforce
- Airtable automation action: Create post in Facebook Pages
- Airtable automation actions: GitHub Issues
- Airtable automation action: Hootsuite post
- Airtable automation action: Send Twilio SMS
- Airtable Actions
- Airtable Bases
- Using Airtable Cobuilder
- Airtable bases overview
- Creating and managing Airtable bases
- Structuring bases in Airtable
- Moving bases between workspaces in Airtable
- Creating and managing tables in Airtable
- Creating Airtable base share links
- Importing third-party data into Airtable
- Using insights in Airtable
- Troubleshooting Airtable base performance
- Airtable Betas
- Collaborating in Airtable
- Airtable Enterprise Support
- General Enterprise information
- External badging in Airtable
- Using app library and components in Airtable Updated
- Ask an Expert beta overview
- European data residency at Airtable
- Airtable user groups overview
- Airtable Enterprise API
- Creating and managing data retention policies in Airtable
- eDiscovery APIs in Airtable
- Airtable and data loss prevention
- Accessing Enterprise audit logs in Airtable
- Set up Jira Server / Data Center to connect with Airtable
- Admin panel pages
- Airtable admin panel overview
- Users - Airtable enterprise admin panel
- Airtable admin panel user details
- Groups - Airtable admin panel
- Workspaces - Airtable Enterprise Admin Panel
- Bases - Airtable admin panel
- Interfaces - Airtable admin panel
- Data sets - Airtable admin panel
- Managed apps - Airtable admin panel
- Components - Airtable admin panel
- Reports - Airtable admin panel
- Settings - Airtable admin panel Updated
- Managing Enterprise organizations
- Managing Enterprise admins in admin panel
- Using Organizations
- Organization branding for apps in Airtable
- Enterprise Hub in Airtable
- Enterprise Hub: Org unit assignment with user groups Updated
- Deactivating, removing access, and reactivating users in the admin panel
- Managing user access to workspaces and bases
- Airtable Enterprise Key Management Updated
- Custom terms of use New
- Enterprise SSO
- General Enterprise information
- Airtable Extensions
- Airtable Fields
- Fields Overview
- Attachment
- Date-based fields
- Formula
- Getting Started with Formulas
- Formula Foundations
- The essentials of Airtable formulas
- Formula writing tips for beginners
- Troubleshooting formulas
- Basic calculations
- Conditional statements
- Logical arguments
- Working with dates
- Displaying DATETIME_FORMAT using the date field in Airtable
- Working with date functions in Airtable
- Calculating the difference between dates in Airtable
- Supported DATETIME_DIFF unit specifiers in Airtable
- Supported DATETIME_FORMAT format specifiers in Airtable
- Using the DATETIME_PARSE() formula in Airtable
- Working with timezones
- Record functions
- Text functions
- Numeric functions
- Common Solutions: Beginner
- Common Solutions: Intermediate
- Common Solutions: Advanced
- Long Text Field
- Linked Record Field
- Linking records in Airtable
- Limiting linked record selection to a view in Airtable
- Dynamic filtering in linked record fields
- Linking to one, many, or a subset of Airtable records
- Converting existing fields to Airtable linked records
- Reordering record links in Airtable
- Understanding linked record relationships in Airtable
- Number-Based Fields
- Other Fields
- Rollup, lookup, and count fields
- Select and user fields
- Integrating with Airtable
- API
- Getting started with Airtable's Web API
- Creating personal access tokens
- Airtable Webhooks API Overview
- Service accounts overview
- Airtable Web API - Using filterByFormula or sort parameters
- Airtable API Deprecation Guidelines
- Airtable API: Common troubleshooting
- Managing API call limits in Airtable
- URL length limitations for web API requests
- Integration services
- Third-party integrations via OAuth overview
- Troubleshooting disconnected OAuth integrations in Airtable
- Options for integrating with Airtable
- Third-party integrations - Common troubleshooting
- Low-code integrations - Common troubleshooting
- Integrating Airtable with external calendar applications
- Visualizing records from Airtable in Tableau
- Visualizing Airtable records in Microsoft Power BI & Power Query
- Integrating HubSpot with Airtable
- Using Zapier to integrate Airtable with other services
- Using Zapier's Multi-Step Zaps to find and update records
- Using IFTTT to integrate Airtable with other services
- Integrating with AWS Lambda & DynamoDB
- Developer tools
- API
- Airtable Interface Designer
- Interface Designer overview articles
- Interface layouts
- Interface elements
- Adding and removing elements in interfaces
- Adding layouts to interfaces
- Formatting elements in interfaces
- Interface element: Button
- Interface element: Calendar
- Interface element: Chart
- Interface element: Filter
- Interface element: Gallery
- Interface element: Grid
- Interface element: Kanban
- Interface element: Number
- Interface element: Record picker
- Interface element: Text
- Interface element: Timeline
- Learning and Resources
- Managing Airtable
- Airtable Policy
- Airtable Records
- Airtable Sync
- Airtable Views
- Airtable Workspaces
- 25 Apr 2024
- 7 Minutes to read
- Print
- Share
- DarkLight
- PDF
Visualizing Airtable records in Microsoft Power BI & Power Query
- Updated on 25 Apr 2024
- 7 Minutes to read
- Print
- Share
- DarkLight
- PDF
Note
While these articles are meant to serve as helpful guides for our customers, 1:1 support for integrations with 3rd party tools is limited in support interactions
If you are experiencing setup issues or need help troubleshooting this integration, we recommend you search threads in the Community first
If your question doesn't appear to be asked in the Community, then post a new message on the Development & APIs board (Requires signing into the community)
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 & 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.
Using Power Query, you 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 a Personal Access Token with the
data.records:read
scope enabledThe 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.
Step 1: Copy Template M Query
Note
The first record in the table or view that is being integrated from Airtable should have a populated value in each field that you would like to visualize in Power BI. If this is not the case, then that field will not appear in PowerBI. Leveraging a specific view with filters and/or sorts can help to ensure that records missing data in certain fields are not the first record called by the API. As an alternative to the table-based query shown below, the query for a specific view can be found here.
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 PERSONAL_ACCESS_TOKEN
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( () => [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",[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]])),// retrieve results per call
Counter = [Counter]+1// internal counter
],
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"
Note
If the above script seems to be causing an error, then please refer to this GitHub resource, as the code may have changed.
Step 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.
Step 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.
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 PAT/IDs identified below.
PERSONAL_ACCESS_TOKEN | BASE_ID | TABLE_ID |
---|---|---|
Your personal access token. | The ID of the base you want to query. | The ID of the table you want to query |
You can learn how to create, update, and delete PATs 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 PAT will start with the letters pat | Your Base ID will start with the letters app | Your Table ID will start with the letters tbl |
Step 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.
![](http://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/integrations_power_bi_queries_pat_01272023.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.
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.
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 Personal Access Token in the actual query using the Authorization header in the request, so there is no extra information to provide here.
The expression error The 'Authorization' header is only supported when connecting anonymously
may appear in an M Query. This error can be fixed by updating the authentication to be “Anonymous” globally in “Data Source Settings” → “Global Permissions.” This must be done for all Airtable endpoints being used to resolve the issue. To open “Data Source Settings”:
Power BI Desktop - File tab → Options and settings → Data source settings
Excel - Data tab → Get Data → Data Source Settings. This can also be accessed from the Power Query Editor.
FAQs
How do I connect Airtable to Microsoft Excel Power Query?
The process here is very similar to the process 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.
How do I automate refreshes of my 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.
Certain field types in Airtable seem to return unexpected values, is there anything I can modify to change this?
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.
What do I do if I run into the expression error: We cannot convert the value null type to type Record
when using a PAT (personal access token)?
We cannot convert the value null type to type Record
when using a PAT (personal access token)?First, ensure you have followed step 3 above, Create Parameters. Then, in PowerBI, navigate to Options and Settings -> Data source settings -> Edit Permissions -> then change Credentials Type to "To Anonymous from Windows." This change must be TO ANONYMOUS, FROM WINDOWS. If the error persists, we recommend reaching out to PowerBI support for further assistance.