Power Query Missing Columns

[Joanna_Parker]

Hi All,

I use the API to pull our data into Excel for automated reporting, and it does the job quite nicely :slightly_smiling_face:

However, I’ve added a new column to collect a bit more information, but that column doesn’t seem to be available to select when choosing columns within the query.

It’s a number field (although I have tried changing it to a text field, and a formula field in case it was the field type that was the issue).

Any thoughts?

[Justin_Barrett]

Were you able to find a solution to this problem?

[Joanna_Parker]

Not yet I’m afraid, any clues?

[Justin_Barrett]

Could you please share more specific details about the setup that you’ve created? Your initial description gives a pretty broad overview of things, but more details are needed to really understand what’s going on and where the problem might lie. If you can share screenshots, that would be very helpful (even if you have to doctor them for privacy).

[Joanna_Parker]

Sure :slightly_smiling_face:

We use Airtable to record attendance of activities within our organisation. I then use the API to do a Power Query within Excel. This means my colleagues involved in comms can see anonymised numbers attending (total and unique individuals) within Pivot Tables, with the option to filter to their date range/activity/activities of choice.

This is possible, because we link the activity record, to the individual’s record from another table within the same base.

So, on the Power Query, the column being pulled through for these Pivot Tables is a column called ‘Students’ that contains only linked records.

We have since started a new activity that does not require linking individuals, and it wouldn’t be the right fit for the activity either. The session leader just does a headcount instead, as comms only need to be able to say how many people attended in total.

As this obviously wouldn’t work within the ‘Students’ column, I created a new column called ‘Number of Attendees’. As mentioned before, I have set this as a text, number, and formula field (typed the number in a separate column, to see if picking it up through a formula field would work) to try and get it to pull through to the Power Query.

When editing the Query in Excel, when I go to ‘Choose Columns’ at the top the ‘Number of Attendees’ never shows up on the list of columns available regardless of the field type.

It’s not an urgent fix if there aren’t any immediate solutions/ideas for now. I’ve set up a weekly email digest instead.

It would just be nice to get all the data on the same dashboard :nerd_face:

[Justin_Barrett]

Sorry for taking so long to get back to this. I don’t use Excel so I can’t help from that end of things. My only other ideas are these:

• Is the query referencing a specific Airtable view and not just a table? If it’s targeting a view, and that view doesn’t have the new field visible, that might be the issue.
• Is there a way to force Excel to refresh its connection? With Make (formerly Integromat), a module won’t automatically recognize added fields in Airtable unless the user clicks “Refresh” next to the table name in the module config. Perhaps there’s a similar feature/option in Excel to force it to look at all current fields, not just those that were present when the connection was first made.
• Perhaps making a new connection/query would solve the problem. This would effectively force a new connection to collect all current fields, though I’m not sure what other steps this would also require you to redo in Excel.

[Joanna_Parker]

Hi @Justin_Barrett,

No problem at all, thanks for taking the time to reply. The query is definitely referencing the table so that ones out, and the connection does tend to pick up new columns that I’ve added in the past so I think that might be a no-go too.

I’ll be moving our dashboards into Power BI using the same type of query, so maybe a fresh connection could be what is needed.

I’ll keep having a play, but thanks so much for your help :slightly_smiling_face:

[user]

Were you able to solve the issue? 

I get new columns when I connect to a view, but I can only get 100 rows when doing so

[HaploSartan]

Just make sure you have entries in all the first fields of the first record. That worked for me.

[ryan10]

I ran into this same problem with the Airtable-provided code example in the documentation. 

You can replace the existing Expand Records step with the following, which should check columns across ALL records:

   // Dynamically expand the fields within each record
   // Collecting all field names from all records
   allFieldNames = List.Union(List.Transform(#"Reordered Columns"[_airtableRecordFields], each if _ is record then Record.FieldNames(_) else {})),
   uniqueFieldNames = List.Distinct(allFieldNames),

   // Expand the 'records' list to individual rows
   #"Expanded Records" = Table.ExpandRecordColumn(#"Reordered Columns", "_airtableRecordFields", uniqueFieldNames, uniqueFieldNames),