Exporting Airtable Data into PowerQuery

[Edd_Tillen]

I've used this very helpful page to be able to export my tables out of air table and into Power Query, and from there into Excel: Sending Airtable Data to Microsoft Power BI & Power Query | Airtable Support

However, I'm having an issue that some seemingly random columns aren't being picked up by PowerQuery. I've done this with 5 tables, some have worked perfectly, some haven't. The columns that are missing are different types, sometimes check boxes, sometimes free text fields. I can't work out what the pattern might be, and therefore how I might fix it.

I'm hoping someone might have come across the same issue in the past, and know exactly what I'm talking about and how to fix it.

[ScottWorld]

Sorry, I don't know the answer to your specific question, so hopefully someone else with more PowerBi experience will be able to chime in below and help you.

The only advice that I can personally offer is to try an alternative, possibly much easier way of getting your data from Airtable into Microsoft Power Bi. For example, the tool that I use for this sort of thing is Make, which offers native Airtable modules and native Power Bi Modules.

Otherwise, someone else will have to chime in with another answer for you. Most of the top Airtable experts that I personally know have stopped posting in these forums, because it's too difficult for us to post comprehensive questions & answers here, now that they've changed the forums. If you don't get an answer here, we've got top Airtable experts who are answering questions at TableForums.com. So you might want to ask your question over there as well, if you don't get a good response here.

[Edd_Tillen]

Thanks for the advice. I hadn't even considered third party solutions. It's frustrating, because this is working, it's just missing a couple of seemingly random columns. The really frustrating thing is, out of 10 columns, I only really need a couple, and can bet your house that the columns it's skipping are the ones I need to use. I'm 90% to where I need to be, but that last 10% is causing a real headache.

[ybai · Accepted solution]

Have you made sure that there are no blanks in any parts of your data (e.g., blanks or nulls are all filled with the text string 'NA')? Power BI will not recognize AirTable columns if the first row happens to be blank.

[Edd_Tillen]

I think you've cracked it. I did know that so it sounds dumb that I fell into that trap. The top row in AirTable has a result in that column, but when the table is imported into Power Query, it puts a different row at the top, which doesn't have an entry in that field. Unfortunately it's a tick box field, so I can't put N/A. It would be interesting to know why it's in that order, but what I'll probably do is a hidden formula column that just converts the tick box to a Yes or No.

[Rafael_Oliveira]

The cause of this problem is that PowerQuery pick up a record as example to create a matrix table. If the record picked up by PowerQuey has a blank field, this field will not be created in the matrix.

In order to avoid this happens and not be necessary assure non-blank field in all records, you could change the script in PowerQuery. This change will let PowerQuery runs all records from Airtable and so will create the matrix table based not in one random record, but in all. If you have a field that is blank in all records, this field will not be created as well.

First of all, you have to create a list of fields

List_Fields = Table.ToList(Table.SelectColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Reordered Columns", "Custom", each Record.FieldNames([_airtableRecordFields])), "Custom"), {"Custom"}),{"Custom"})),

And then you will pass this list as argument of the Expand record function:

#"Expanded Record Fields" = Table.ExpandRecordColumn(#"Reordered Columns", "_airtableRecordFields", List_Fields),

[bibiczh]

If I were you, I would export your data on Excel through the app "Excel-Exporter". They have a freemium version, so you can make some tests to see if it's done the way you want. 

https://excel-exporter.com/dashboard/app

[GavinA]

If you'd like to skip the whole power query aspect you can format your data export using CSV Getter. Checkout this guide for importing into excel:

https://www.csvgetter.com/blog/export-airtable-to-excel

[Public_Knowledg]

Can you elaborate a little about where to insert the code?

Here is M Query code from the Airtable Support site.

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"

Where can I put/insert your suggested codes: List_Fields = Table.ToList(Table.SelectColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Reordered Columns", "Custom", each Record.FieldNames([_airtableRecordFields])), "Custom"), {"Custom"}),{"Custom"})),

Is it after the #Reordered Columns? and before the #Expand Record Fields?

[Public_Knowledg]

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"

 Hi @Rafael_Oliveira , I incorporated your codes into the M Query and was able to extract all the fields/columns from the Airtable table even though a column may have missing/blank values. The final M Code is pasted above. Thanks.