Nov 25, 2022 04:47 AM
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!
Nov 25, 2022 12:36 PM
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:
Dec 05, 2022 06:21 AM
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
Feb 22, 2023 09:39 AM
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
Jun 13, 2023 12:18 AM
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
Sep 14, 2023 12:00 PM - edited Sep 14, 2023 12:01 PM
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.