Help

Re: Linking multiple Airtable tables to PowerBI

1075 0
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!

5 Replies 5

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.