Help

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

Topic Labels: Scripting
Solved
Jump to Solution
13976 20
cancel
Showing results for 
Search instead for 
Did you mean: 
Rhode_Ninja
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

20 Replies 20

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
4 - Data Explorer
4 - Data Explorer

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

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.

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
4 - Data Explorer
4 - Data Explorer

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

ithamar_hardy
4 - Data Explorer
4 - Data Explorer

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
4 - Data Explorer
4 - Data Explorer

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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#)

 

Screenshot 2023-09-09 185103.png

(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_IDTABLE_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:

Screenshot 2023-09-09 184917.png

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.

petrs
4 - Data Explorer
4 - Data Explorer

My issue was in copy&paste of the IDs ... one thing mentioned above and not clear from the manual is just copy the text of table ID starting by tble until first  /. That is probably clear for most of the users... but be careful when copying the app ID.. I accidently copied also the dot (.) at the end of it since it is a small character I did not notice it for a while ... but you can see in your table link (just before the tble) that the app ID does not have any dot... hope that helps!

JakubCh
4 - Data Explorer
4 - Data Explorer

Hi, I had issue with downloading data from Airtable. There was just empty table, but when You delete some steps and look into the table You cane find information that Authorization header can only be applied to anonymous credentials. I have followed the instructions found in this forum (changing credentials to anonymous) but it did not help at all. I was still getting the same error. The solution was to change credentials not only in this specific report but globaly (You can select it in the credentials settings menu on top). Find all endpoints with airtable (I had two, not just one as in the local settings) and change to anonymous in all of them. Let others know if it worked for You by giving kudos to this reply. Wish You luck! 🙂