cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GrootCRM
Advocate I
Advocate I

Access data through Odata feed using Excel or Power Query

I would like to give customers access to their own data using an odata feed within the Power Portal. Of course the customer has to use a login to access the data. Did anyone tried this before?

 

I tried to connect to the odata feed using Excel / Power Query. Unfortunately I am not able to get access to the feed. Not with the basic login, nor with a organization login (AD login). Somehow an organization login isn't possible in Power Query with this type of feed.

 

Hope someone can help me!

Thanks a lot!

 

Best regards,

Elowy.

5 REPLIES 5
OOlashyn
MVP

Hi @GrootCRM ,

Please do not use the OData option for Lists - it is not secure as it doesn't respect table permissions. In general, Portal is designed as a website to access data, not as an integration or data point. 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.
GrootCRM
Advocate I
Advocate I

Hi @OOlashyn ,

 

Many thanks for responding. Can you explain this a little more, please? As far as I know, when checking "Enable table permissions", the permissions are being used. Or is there some kind of flaw I'm not aware of?

At this moment the users can login, view their data in a list, and click on download to get this data in Excel. Isn't this about the same as giving them Excel access through a (secure) odata feed?

 

Regards,

Elowy.

@GrootCRM , yes my bad - after last year's security problem MS started enforcing table permissions for odata as well. Force of habit 😃 But to enforce those table permissions webpage needs to know which user is currently logged in and I don't know how you can do that with Excel access. 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.
Piper
Resolver II
Resolver II

Hi @GrootCRM,

I'm not 100% clear if this fits your use case so please keep that in mind. 

 

You could create a Virtual Table in Microsoft Dataverse. Then you can surface that table in your portal and enable the appropriate table permissions. Be sure to check out the Limitations of Virtual Tables as well.

Thanks,

Piper

 

 

GrootCRM
Advocate I
Advocate I

Hi Piper,


Thanks for responding with a great suggestion. However, I now see that my question was not totally clear. Sorry about that...

 

I'm trying to give portal users access to the Dataverse data using odata and Power Query in stead of Portal lists. So they can make their own dynamic queries on the Portal data.

 

As long as the odata feed is not secured, this works well. But as soon as I try some kind of security (like AD or native login), I'm not able to connect to the feed anymore.

 

Thanks,

Elowy.

Helpful resources

Announcements
Welcome Super Users.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (1,926)