Help

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

Solved
Jump to Solution
6607 0
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.

1 Solution

Accepted Solutions
karkumar
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

See Solution in Thread

19 Replies 19
karkumar
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

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.

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? 

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."

Great, Thank you! 

ithamar_hardy
4 - Data Explorer
4 - Data Explorer

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

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

Rhode_Ninja
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.

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! 🙂