+9Hi 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?
+21Were you able to find a solution to this problem?
+21Not yet I’m afraid, any clues?
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).
+9Sure :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:
+21Sure :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:
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:
+9Sorry 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:
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:
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:
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
+2I 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),
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.