Help

Connecting Airtable to Power BI

Topic Labels: Importing & Exporting
664 0
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"

 

0 Replies 0