Not all table columns loaded to Power BI

[Moustafa_Toulan]

Only 57 out of 98 columns are loaded through the query HERE in Power BI. Does anyone know how can this be fixed?

[SamF]

I have the same issue! I’m using the M code provided by Airtable and it works fine, except that only about half of the fields are being captured. There seems to be no correlation to data type, column order, anything. It affects all bases. Any help would be appreciated!

[SamF]

Aha! After some testing I determined that the columns weren’t being imported because they contained no data. I had built the structure in Airtable but hadn’t populated it with much data, and once I had entered some input, the columns were available through Power Query. Hope that helps!

[Moustafa_Toulan]

Thanks Sam for the feedback! in my case the columns I’m missing contains plenty of data.

[Bruno_Trivellat]

Similar issue here. Columns with almost no data wheren’t being imported, since I populated the empty fields with dummy values they showed up in Power Query.

[Joanna_Parker]

Same here, had this issue with Excel too. Tried different field types to see if there’s any pattern, but no luck.

So, do I need to populate empty fields with something in order for the column to be pulled through?

[SamF]

Yep, I suggest you populate each column with a “TEST” value or similar, and then in PQ you can filter it right out to ensure you’ve got the right data.

[Hidro01]

So according to airtable tutorial on how to load data on PowerBI here

"PowerBI will only load a field if the first record coming from Airtable has data in that field. So if the field is empty, then it won’t show up in Power BI."

This mean if the first record in the database has a field that is empty  that field wont showup, even if in later records that field has data.

Anyone know how to avoid this? besides creating the first record with fake data?

[mario_lamban]

I'm wondering if there's a better solution than populating the first record with dummy text. This a big hiccup for the integration between Airtable and PowerBi. Any solutions?

[Udi_Golan]

Hi Everyone,

After some research + a lot of trial and error, here is a solution.
No need for dummy row or any other change at AT.

Replace this 

 #"Expanded Record Fields" = Table.ExpandRecordColumn(
     #"Reordered Columns", "_airtableRecordFields",
     Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]),
     Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]))
in
    #"Expanded Record Fields"

With this

#"Expanded Record Fields" = Table.ExpandRecordColumn(

#"Reordered Columns", "_airtableRecordFields",

List.Distinct(List.Combine(List.Transform(List.Transform(Table.ToRecords(#"Reordered Columns"),each Record.Field(_,"_airtableRecordFields")), each Record.FieldNames(_)))),

List.Distinct(List.Combine(List.Transform(List.Transform(Table.ToRecords(#"Reordered Columns"),each Record.Field(_,"_airtableRecordFields")), each Record.FieldNames(_)))))
in
#"Expanded Record Fields"

This solution iterates through all the records and merges the columns that appear in each record (then uses distinct).

Thanks to dofinity.com for sponsoring this.
Enjoy!

[mario_lamban]

Thanks for your support, @Udi_Golan!

I'm now facing a different issue, however, so I couldn't try it. I'm trying to replace the API connection with a Personal token connection, but I'm getting an error related to credentials & authentication when I'm trying to fix this other error "We cannot convert the value null type to type Record"

[Udi_Golan]

Just a guess - due to the change you may experience also a permissions change which results with records not being returned and then null value instead.
 

[mario_lamban]

Thanks for the reply, @Udi_Golan! I managed to fix the Token issue by updating the permissions, thanks!
I'm now trying your code replacement to show fields with empty values and it is still not working 😞
Is it as simple as replacing the code as you suggested? There's no need to replace other values?

Thanks

[Udi_Golan]

As far as I remember there was no additional change required.
What is it that you get?

[mario_lamban]

Hi @Udi_Golan. I got exactly the same columns that I was loading with the Query provided by Airtable. Columns whose first record values were empty, were not loaded to PowerBi after replacing the final part of the query according to your recommendation.

[Udi_Golan]

Hi @mario_lamban 
Strange. The solution proposed is totally agnostic to the first record {0}
It runs over all records and extracts the distinct list of field names from all records.
Maybe the fields you expect to show on the BI are empty on all records and not just the first one?
Otherwise I would double check the code to ensure the code that includes the solution above is indeed running and not some other code.
Good luck.

[PabloIvan]

Saved me, thanks a lot! 🙏🏻