Airtable to Power BI using Personal Access Token "cannot convert the value null to type Rec

[Rhode_Ninja]

Hello Community,

First post here. I am attempting to connect my Airtable to Power BI.

I am receiving the following error message in Power BI

• Expression.Error: We cannot convert the value null to type Record.
  Details:
  Value=
  Type=[Type]

I am following this guidance and using a Personal Access Token. I setup Power BI Parameters successfully but received the error code in the Query. I suspected that it may be the M Code so I followed a link to a GitHub resource for alternate code.

• "If the above script seems to be causing an error, then please refer to this GitHub resource, as the code may have changed.

I have tried both versions of the M code.
I don't know enough about tracking down this error code. Any ideas of what it might be?
All assistance is greatly appreciated.

[karkumar · Accepted solution]

Facing the same issue. I too submitted a new post asking this question. @airtable team: Can anyone please help to fix this issue! Thank you!

[Rhode_Ninja]

Hi Karkumar. I used ChatGPT 3.5 to help write code. I went through multiple iterations. Never a syntax error but had several error codes generated in PBI. Everytime I fed the error code back in to Chat it tried a revised solution. Acutally quite amazing. Sadly I still was not able to get in. I feel like I might be missing some basic that I am overlooking - even though I reviewed and reviewed. Still not sure what it is. Try AI and see if you have more luck. Give it a good description of what you are trying to do. Let me know if it works please.

[karkumar]

Thanks Rhode! All I'm trying to do is extract few field records from Airtable to PowerBI for visualization/reports. Initially I used API key & corresponding M query - which was working fine but with PAT, it's failing with above Null type Expression error.
Do you mean using ChatGPT 3.5 helps to get rid of this problem? Did you create the code using ChatGPT and used that query for PowerBI integration? 

[Rhode_Ninja]

I used ChatGPT to write the code. Try it. Write something like this and then have a conversation from there if you get error codes. 

"Can you write code that will allow me to pull data from airtable into power bi and allow pagination for a large table of data? I want the code to work via the personal access token and not via api."

[karkumar]

Great, Thank you! 

[ithamar_hardy]

"Why can't we simply connect using the "web" option in the data source selection of Power BI?"

[isleRoyaleGuy]

The topic says solved but I do not see a solution.  Any help would be awesome!

[Rhode_Ninja]

It is not solved - at least for me. I have used ChatGPT 4 to help write multiple versions of code and it is still not working. If you haven't seen this about blanks - I would try this. Solved: Exporting Airtable Data into PowerQuery - Airtable Community 

I made sure I had no blanks - still can't get it to work.

[Topar14]

I was able to fix the error by making sure that my TABLE_ID was correct. After following the article, I took everything after the "tbl" characters as the TABLE_ID, when I really only needed up to the '/'.

The API documentation clued me into what was actually needed. When I manually tried to expand the table in power query, it was throwing an error that the page wasn't found, which made all the subsequent steps not work correctly and ultimately led to the same error you were getting.

[Rhode_Ninja]

Thanks for posting @Topar14 

I was hoping when I saw your post. Unfortunately, it's not the case for me. Tbl ID is correct.
ChatGPT 4 got me to some shortened fancy code where it incorporated Base ID, Table ID and PAT into the code. I am now at an error code of Edit Credentials. I've tried multiple ways to get past it - Anonymous is what I believe is right. What Credentials choice are you using. Still stumped on what else I can try. Thanks for your post.

[isleRoyaleGuy]

Awesome thank you, I got past that error.   Now have another one; Expression.Error: A cyclic reference was encountered during evaluation.  

[Topar14]

My credentials were set to anonymous as well. I'm pretty new to importing data from an API, so I'm probably not the best resource to ask.

[Topar14]

I found this forum post. I think you need to step though your applied steps to find where your previous step in M Query isn't matching up to the actual applied step shown on the sidebar.

https://community.powerbi.com/t5/Desktop/quot-Expression-Error-A-cyclic-reference-was-encountered-du...

[isleRoyaleGuy]

Do you have to be on a paid tier to have this functionality?

[ithamar_hardy]

It can be incredibly frustrating to spend hours trying to establish a connection that doesn't work. If anyone has successfully connected the two tools, please come forward. I would like to spend my time working on my dashboard, not dealing with a frustrating connection.

[praisechuck421]

This solution for me is that the Parameter Names in the instructions need to match the instructions exactly, meaning they need to be ALL CAPS, just as they are entered in the Power M query provided.

[Kami_T]

Try deleting everything and start from scratch. Be sure to only have one Power BI Desktop application open.
New Source -> Blank Query -> Fill in your parameters here:

Name: BASE_ID Type: Text Suggest Value: Any Value Value: (ID#)

Name: TABLE_ID Type: Text Suggest Value: Any Value Value: (ID#)

Name: PERSONAL_ACCESS_TOKEN Type: Text Suggest Value: Any Value Value: (ID#)

(Please cut and paste directly from the instructions to prevent any mispelling, capsensative)
Here is a video showing you how to locate your Airtable BASE_ID, TABLE_ID, and PERSONAL_ACCESS_TOKEN:

(361) HOW TO, ADVANCED: Airtable linked to Excel using microsoft power query and REST API. - YouTube
You can also click create (PAT) Personal Access Token which will take you there. Remember the URL will only be available once so you would need to save it in a secure location. Personal access tokens - Airtable Web API

Then cut & past "M Query" code (Sending Airtable Data to Microsoft Power BI & Power Query | Airtable Support) in advance editor:

A window will pop up, empty box then paste "M Query". A preview of your table should then appear, once you return to your Power BI hub refresh.

Hope this helps.