Connect to External Tables with Dataverse Virtual Tables in Dataverse (Tutorial) 💡

[@techbrojoe]

Fantastic tutorial. Thank you for making these!

[@PragmaticWorks]

Joe, so glad it was helpful!

[@saxhigh]

Hi Brian, thanks for this - great run through!. Two open questions for me. (1) is there a reference to show how to do this for Excel as an external data source? and (2) Will Power BI be able to Direct Query connect to a Dataverse table driven from this Excel virtual data source?

[@Powerdevpodcast]

Hi Brian, thanks for this. I have used this in a solution and moved to another environment but not sure how to update the reference to the new SharePoint site

[@achyutpsd]

Good one. How about column and row level security for the data. all these are maintained in Dataverse metadata or should be configured in SQL ? . Kindly advise

[@PragmaticWorks]

Yes, all of that is delegated to your SQL Server or SharePoint system. Dataverse maintains just that you have access or don't.

[@dougydoe]

Thanks for this as this is what I really needed to connect to an Azure SQL database. One question though, I would imagine from the SQL side, I still need to configure Firewall settings within Azure so I can authenticate to the SQL. Are there any specific setting about Dataverse I need to be aware of so this can be configured within the Firewall?

[@acm-yo4mp]

Hi Brian, Thanks for this great video. I want to query one thing. When I created the SharePoint virtual table, the single line text columns of SharePoint are automatically configure to Multiple lines of text column type in virtual table. may I know how to solve this problem?

[@vijau15]

Hi Brian, thanks for sharing valuable tutorials. It is very helpful to me. I have another query regarding power automate flow. How we can trigger the automated flow when row added in virtual table. Can you please share it ..

[@RedCloudServices]

Brian does the sql table change limitation still exist a year later? also is there a MS link with the supported data source connector types? love your videos thank you sir

[@sairaj6875]

Thank you for this video. Does anyone know if this works in offline mode like dataverse tables generally do?

[@PragmaticWorks]

It does not yet since it only passes the query through to the underlying data source.

[@henrychoi3809]

Hi Brian, Thank you very much for the video. I am facing a storage issue with one of the table inside Dataverse is over 50GB. I would like to move part of the DB from Dataverse to Azure SQL. What is best way to do that? After I moved those part to Azure DB, then I can apply the method you mention in this video. Thank you very much in advance.

[@PragmaticWorks]

Likely using Azure Data Factory would be the most efficient way to move the data.

[@joshhammz]

Hey Brian, thanks for the video! I was messing around with Virtual Tables from a SharePoint list the other day and I was having trouble with Lookup columns. Nothing I've read says that Lookups AREN'T supported but I still couldn't get it working. Do you have any insights to offer on that? Thanks again!

[@PragmaticWorks]

You can see the limitations of SharePoint as a source here: learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=sharepoint. I'm surprised to see that Lookups are supported based on that doc for SharePoint. Person pickers are not for example. I haven't tried the lookup though for Sharepoitn personally.

[@jt3o5]

Hi Brian, I don’t know what I did wrong but I don’t get that option to create a table from external data. How can I fix this issue

[@sunpengjie4565]

Hi Brian, thanks for the video. When I add the table from SQL as per the instruction, the table can be added but the data can not be loaded from SQL side. It comes back with an error saying something went wron. How can I fix this error?

[@PragmaticWorks]

Sorry, I need some sort of error to debug that one.

[@jacomathew1300]

Hey Brian. I know this is an old video but am running into a roadblock. I have an employee table with Id, FirstName, LastName and few other columns. When I import that table, Power apps ask me to choose a "Primary field". From what I read, this column has to be unique. I cannot select Id as this is set as Primary Key. First name column would not have unique values. Could you suggest a way how I can handle this ? I am unable to create a column on the resulting table. Also in the table where I use Employee Id as Foreign Key, only the 'Primary field" shows up in the dropdown. So I am unable to select correct employee record as there are multiple employees with the same first name. If you have dealt with this in any of you videos, please point me to it. Much appreciated. Thanks.

[@julijazas]

It loads 10k tables only and the table i need is not loaded. What is the workaround?

[@Weatemilly]

I've found a curious issue of SQL bit columns not updating properly. If you update boolean from dataverse virtual table, it becomes true during update,if update is done on server - accurately displayed in the virtual table. Has anyone else come across this?

[@KaraboMasilela-e6p]

I cant seem to login to SQL Server on my desktop , can someone assist ?