Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Connecting Airtable to Power BI

Topic Labels: Importing & Exporting
2591 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Public_Knowledg
5 - Automation Enthusiast
5 - Automation Enthusiast

Based on an earlier post on connecting airtable data to Power BI, some folks, including me, have problems reading Airtable fields/columns that have blank/missing values for some records. Based on an note from a user in the early post, I am able to revise the Airtable M Query code from the Airtable support site (https://support.airtable.com/docs/visualizing-airtable-records-in-microsoft-power-bi-power-query). I added the "List_fields"= line near the end of the query. Now the new M Query can read in all columns / fields of an Airtable table even if they have blank/missing values.

Here is the revised M Query. Hope this can be of help to you.

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"}),
List_Fields = Table.ToList(Table.SelectColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Reordered Columns", "Custom", each Record.FieldNames([_airtableRecordFields])), "Custom"), {"Custom"}),{"Custom"})),
   #"Expanded Record Fields" = Table.ExpandRecordColumn(
     #"Reordered Columns", "_airtableRecordFields", List_Fields)
in
    #"Expanded Record Fields"

 

2 Replies 2
Yerko_Begic
6 - Interface Innovator
6 - Interface Innovator

For Authentication, it is recommended to use "Anonymous" which doesn't work for me no matter what

Yerko_Begic_0-1728591506534.png

My Date Source Settings on current file as well as Global are all set to Anonymous

Yerko_Begic_1-1728591613510.png

Yerko_Begic_2-1728591638222.png

 

 

Thomas991
4 - Data Explorer
4 - Data Explorer

Fantastic! Worked like a charm!
Now if there only was a way to get the values from linked records as well, then the AT PBI import would be even more useful