Help

Re: Linking multiple Airtable tables to PowerBI

1692 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Peter_Van_P
4 - Data Explorer
4 - Data Explorer

Hi,

following these instructions Sending Airtable Data to Microsoft Power BI & Power Query | Airtable Support I was able to link a single Airtable table to PowerBI and this works very well. However, I would like to link multiple tables from the same Base to PowerBI. Does anyone know how to solve this?

Cheers!

11 Replies 11

Welcome to the community, @Peter_Van_P!

Sorry, I don’t know the answer to your question because I’ve never used PowerBi nor M Query before (and I also don’t have a Windows computer).

I actually don’t know if we have too many people in these forums that have experience with those tools, but if we do, hopefully they will chime in below to help you out.

In the meantime, however, what I do know is that Make supports PowerBi, so it might be easier to do what you want to do with Make, since it’s a no-code/low-code integration & automation tool:

tjonesy10
4 - Data Explorer
4 - Data Explorer

Hi Peter,

I think you need to create an additional TABLE_ID parameter in Power BI with info from the second table. I would name the parameter something different (TABLE_ID_2, for example) and make that change in the additional query in Power BI that you'll need for this table, too.

Assuming you're using the M query in the advanced editor, I'd find and replace "TABLE_ID" with "TABLE_ID_2".

Hope that helps,

Tim

Carolyn_Felix
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Peter -- I'm experiencing the same issue (only able to pull in one table of data using M query advanced editor. Did you find a solution that allows you to link multiple tables from the same base using Power Query? 

https://support.airtable.com/docs/visualizing-airtable-records-in-microsoft-power-bi-power-query

 

HansZenth
4 - Data Explorer
4 - Data Explorer

Hi Carolyn and Peter,

Sorry for late reply - but did not pay attention to this thread until now. The key here is to convert the query into a function - and then reference that function in a number of queries = one for each table you want to import.

Below it the slightly updated code, which can be pasted directly into a blank query - that will give you the function. Then just play with the "invoke" to get a query that references the function.

Enjoy

/Hans

let
Source = (TABLE_ID as text, PERSONAL_ACCESS_TOKEN as text, BASE_ID as text) => 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"
in
Source

 

Kami_T
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much for sharing this @HansZenth !!! You seriously saved me so much time and a headache. I truly appreciate your kindness in sharing your solution with the community.

Davidt
6 - Interface Innovator
6 - Interface Innovator

I cannot load the data. I cannot get past the authentication part. I set the method for accessing data to "anonymous" but I get an error saying that the authentication couldn't be completed with the entered credentials. Is this a known issue related to how my company might have set access permissions at organisation level?

There is nothing magical on the auth part in this solution. You need to configure your personal access tokens and you need to have adequate access rights on the AirTable side.

As a first step, create individual queries on the tables you try to access, and validate that it works with your PAT. Then consolidate and utilize the function call mechanism described above.

https://support.airtable.com/docs/visualizing-airtable-records-in-microsoft-power-bi-power-query is the official support article on this subject and a good starting point.

Davidt
6 - Interface Innovator
6 - Interface Innovator

Hm yeah I am aware of that article and have followed those instructions and don't see where I missed something.

You say "personal access tokens" and "and you need adequate access rights on the AirTable side". I find this a bit misleading, because in fact I only need a single PAT, right? Or are there any other "adequate access rights" that I have to enable for this to work?

...I do have a PAT with data.records:read scope enabled so I'm still stumped. If your read the support article closely, you see that Airtable is suggesting "If the error persists, we recommend reaching out to PowerBI support for further assistance." which hopefully can resolve this issue on the PowerBI / Microsoft side.

Hello again,

The reason I write token(s) is based upon the fact that this article is about multiple tables in AirTable - depending upon your AT config, you may need multiple PATs.

To make sure your PAT is working, I would recommend to utilize curl or Postman to e.g. list out all records in one of your tables. Simply to validate that your PAT is correctly configured and with adequate rights.

If that works, the issue is indeed in the PowerBI end - and if you are sure the PAT and base/project information/tokens are setup correctly, my initial suggestion would be to clear all permissions within Data source settings in the query part of PBI. When you refresh your query, you will be asked to reestablish the permission level for the data source - thereby ensuring that you do not have any erroneous "leftovers"