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:

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

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

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

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.

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.

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"

I actually was trying out accessing the data via postman and surprisingly, also there I had authentication problems. So it seemed i'm getting something wrong, unrelated to powerbi...aaaaaaaaaand i found another post on the community forum that actually saved me: I was using the token snippet instead of the full token, just as this other user did. I know, sounds stupid, and it is 😄 Thanks @HansZenth_Work for your help! 

Multiple tables from airtable in powerbi now. Nice. Is it normal that a mere 1500 rows will take like 3 minutes to load? And as a Powerbi beginner i don't know this: How can I stop powerbi from pulling new data every time i transform something in powerquery? I don't really want the data to be pulled anew into memory from airtable every time I apply a filter or something...is this possible?

Hans I have not been able to find how to show the values in linked record fields as the just show list. I assume you

1. pull in the linked tables

2. extract the list which appears to be the airtable record ID

3. merge the queries by this ID 

4. expand the linked record column. 

Is this the only way? If not is it the most efficient way?