How to use SharePoint Lists in Model-Driven Power Apps with Dataverse Virtual Tables

You can now build a model-driven Power App using your existing SharePoint lists, without needing to integrate or move your data. In this tutorial I show you how to create no-code virtual tables in Dataverse, create relationships between your SharePoint list data and other tables in your model-driven app, and test out the real time updating of data in both directions. And yes, I get a bit excited about it!
****************************
Connect with me:
Twitter: / lisacrosbie
LinkedIn: / lisa-crosbie
Take my LinkedIn Learning Course: Microsoft Power Platform Fundamentals (PL-900) Exam: Power Apps linkedin-learning.pxf.io/eKR4o6
****************************

[@chadalthaus3866]

Great walkthrough of AMAZING functionality! Now I need to go try this out for myself.

[@santhuthgreat]

Hey Lisa, last one minute of video is precious and very useful 👏👏

[@LisaCrosbie]

Thank you for watching all the way to the end!

[@brendanbowen7097]

This IS super exciting! Thanks for sharing this!

[@LisaCrosbie]

You bet!

[@denisskorikov6234]

Great content! Thanks for sharing it!

[@dododudu9641]

😍 I´m in love! please post more!

[@sirwonkalot]

Yes - Very exciting indeed! Thanks again for another great video, please keep it up. I have been getting my other associates to subscribe due to your clean/clear/direct method. I hope you will have some training on advance topics dealing with model-driven app search/indexing in dataverse (capabilities/limitations/advanced). Thank you again Lisa.

[@LisaCrosbie]

thanks so much, I do plan to do a video on dataverse search actually, I'll bump it up the list.

[@henryburkhardt4160]

Hi Lisa, thanks for sharing this quick tutorial with us. This new wizard looks very promising. What I can not figure out is how to propagate structural changes from the SharePoint list to the virtual table. Let's say I add a column or change a data type of a column. I think schema change would be the correct term. It is not automatically reflected in the virtual table. Thanks in advance 😊

[@bendunford8732]

I have the same question--thanks for asking, Henry.

[@kellyliu4225]

Great video and awesome feature! Is it any concern about the security for Dataverse Virtual Tables and SharePoint?

[@KorniPesut]

wuhuuu !!! this is so great.

[@QaisNL]

Amazing👍👍👍

[@jasonhensley4782]

Looks Great Amy - Thanks. Is it possible with a model driven app to bring data in via an API? e.g. Customer list stored in MYOB.

[@PankajSingh-po7ti]

Hey Lisa, Thanks for this Video, Will this also work if I have a field in my SharePoint site with data type person or group?

[@patrickleavydatadrivenfina1491]

Hey Lisa, great videos! Have you ever tried to build a job management system with power apps? my SMB clients need that kind of thing: take a job number through the system all the way to invoicing. invoicing would be in xero.

[@user-lo9eg9tc2r]

Thank you for the nice video Lisa, but it seems that the Virtual Table can not get the look up column and person column data in sharepoint list

[@denisskorikov6234]

How does the security looks like for this approach? Access only for Virtual Table is enough? Or you also need to give an access to the Sharepoint list? Many thanks in advance!

[@peterhale9517]

Export / Import etc - does everything still work as intended. What happens if you have a SharePoint site for Dev and another for UAT , Production etc?

[@haziqaiman1687]

can the attachment also can be pull into the dataverse or how it will work? im having sharepoint list that only purpose is to store attachment

[@Romaine.L]

Lisa, you are amazing. I'm thrilled to have found your channel. @2:50 I get the error "Something Went Wrong. We weren't able to open your table. Try reloading or reopening." Any idea wat tat might be about? None of the data seems to load in at all.

[@alielturk2534]

Encountering the same problem. Please help.

[@garmatik]

Thank you so much Lisa - great Video! Could you do a Interface to SAP as well like this?

[@LisaCrosbie]

Not using this method but you might be able to do it using code with virtual tables if you have an APi

[@namhoangtran1987]

Good job Lisa, is that virtual table caculated into dataverse instance storage? Or just storage of Sharepoint?

[@chadalthaus3866]

Virtual tables don’t actually house the data in Dataverse but dynamically point to the data source. Which means there should be no capacity consumed!

[@mkhijazi]

Thanks Lisa, there was a relationship manager field in the SP list, is it supported, and if it's not supported Dataverse will automatically ignore it at the time of table creation?

[@LisaCrosbie]

That was a Person type column in SharePoint, and you're right, it didn't come across - based on the current known limitations in preview at the time of writing this, that column type isn't supported. learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=sql

[@ramirosousa]

Hi Lisa, First thanks for this video. It is very awesome. I just have a question. The records will be saved on SharePoint List and just will show on dataverse? Or the record will save in both of them? I just want to know if external table record will use dataverse capacity.

[@LisaCrosbie]

It’s a virtual table in Dataverse, it is only actually stored in SharePoint

[@mhripko]

Lisa, great and exciting video on SharePoint as a virtual table. I've mastered SharePoint as a virtual table but still cannot figure out how to do it with Excel. Any chance you can put your suppliers into an Excel table in a workbook, and then create a virtual table from the Suppliers table def inside of the excel workbook? Everyone says it can be done, but I've yet to find someone who can demonstrate it from start to finish like you do here....

[@LisaCrosbie]

I think it can be done but it isn’t a drag and drop low code thing. This option I show here is only currently available for SQL and SharePoint.

[@paspuggie48]

@@LisaCrosbie I'm not sure if it can connect to one's workbook directly but I do know if one creates a Dataflow then it is possible because I have done it recently. I am a basic M-D App developer but use Power Query daily to develop solutions for my customers. The App I'm learning to develop (with help from your videos 😉) requires data from multiple Excel workbooks and text files. Using the Dataflow option in your Solution I'm able to use the built in Power Query editor to get & transform the data from my files. Once that data is inside the PQ Editor I can then combine / merge the data to how I want my final Table to look like. Once you create your Dataflow it will automatically add the Table which you can select in your Solution to create Forms/Views etc The huge benefit of this process is one can schedule the Dataflow "refresh", so if one's Excel docs are updated then the App will update with that information. This is incredible because Power Query in desktop Excel does not have a scheduled refresh option (one has to physically open the document and press refresh by hand) but with Dataverse it is possible..it's a gamechanger for me 😇

[@PeterHarket]

This is awesome! How does this work with the row limitation in sharepoint? Is this now increased to “infinite” to match dataverse?

[@mattmoncelle2506]

I have the same question!

[@LisaCrosbie]

As far as I know it doesn't actually change any of the capability or capacity of SharePoint. It allows Dataverse to connect to what's already there. If you want that huge data capacity you'd need to work with an actual Dataverse table.

[@jt3o5]

Hi Lisa, I don’t get the option to get table from external data

[@beardenbob6567]

Is it possible to do something analogous in reverse - for example, surface a Dataverse table from a SharePoint list? For example, add a List column that's a lookup from some Dataverse table? Or maybe this already exists?

[@LisaCrosbie]

You can't bring Dataverse into SharePoint as far as I know, interesting idea though.

[@diegoalejandrograneros4926]

This stops working the moment that you have a CHOICE column in MS Lists, Dataverse does not connect.(to date) Do you have a solution for this maybe? Thank you!

[@clausdev85]

Great Video :) Are there any known limitations on the types of SharePoint columns that can be used in Dataverse?

[@haoranzhang4966]

you cannot create chart based on the virtual table

[@LisaCrosbie]

Yes, at the time of creating this video and writing this comment, it's still in preview and there are some limitations - you'll find them documented here learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=sql

[@KenChiwo]

Is there a method for the reverse, where we want to connect a model-driven power app table in a SharePoint List?

[@LisaCrosbie]

Not that I've seen at this stage.

[@mardithpascua8112]

I tried it today but an error occurred: Could not find an entity with name none and id 00000000-0000-0000-0000-000000000000 (4103225). I remove the choice column from the SharePoint but the error still occurs. Did you got into this kind of error?

[@azaty]

Can we have a form to let users modify data? or is it for read only purposes only

[@LisaCrosbie]

Yes, if you watch the part towards the end of this video I show the model driven app form which can be used to edit existing rows in the connected SharePoint list or create new ones.

[@idah2829]

Will the data automatically sync to the dataverse table as people update the sharepoint list? or is there a way to set up that automation?

[@LisaCrosbie]

It doesn’t actually need to synch, because it’s a virtual table it’s just like another way to view and interact with the original list. What I show in this video with the data updating both ways is the behaviour, I didn’t do anything to set that up other than what I showed

[@idah2829]

@@LisaCrosbie Amazing, thank you for your knowledge and insight!

[@zaidpatel8695]

Can we circumvent the licensing requirement around DataVerse with Power App by using List as an alternative datasource?

[@LisaCrosbie]

No, you still need a full Power Apps license to do this.

[@geralddahl9159]

Follow up unlicensed scenario question: I can make non Dataverse licensed Power App and connect it so that it has crud functionality with a SharePoint list. The changes users of that app make in the SharePoint list will be seen by me (a licensed Dataverse user) as soon as I use your method to connect my model driven and canvas apps to that same SharePoint list. And finally, even after a Dataverse licensed user such as myself connects to that SharePoint list, the original non Dataverse licensed users can still contribute to that SharePoint list without being required to update their license to include Dataverse? Are my statements above correct? Thank you.

[@s2000jan]

I tried it out to bring in a Dynamics table from sql server but it did not work because it could not create a guid from the primary key (which was a text field)

[@LisaCrosbie]

Not sure what you're trying to do here, Dynamics is already on Dataverse, not SQL?

[@s2000jan]

@@LisaCrosbie Sorry, I meant I wanted to load a Navision table (on premise sql server)

[@SmartLearningAI]

Does this work with Microsoft Lists in the same way?

[@LisaCrosbie]

No, what I'm showing here is just for SharePoint lists or SQL tables at this stage.

[@davidzhou9085]

I didn t have table asset how can i get it ?

[@LisaCrosbie]

I created it as a custom table. You'll find a full tutorial here if you want to learn how to create custom tables in Dataverse kzbin.info/www/bejne/gnqmaXd8jJ2Zhac

[@yayak.9726]

Is it possible with document library!

[@LisaCrosbie]

Document library is a different approach, SharePoint documents are natively integrated with records in a model-driven app - e.g. you view an account and see all related documents (stored in SharePoint)

[@yayak.9726]

@@LisaCrosbie thanks, my case is having an action form (pdf file) and should be signed by multiple users, I will use canvas as I have show/hide many controls according to current user as well as filtering records. The action form is stored in SPO. and I have a dataverse table as file reference such as assigned to, status, reference number, and I need to make a relationships between this table and document library ( many to one). Is that possible!

[@masgitz]

Does this trigger premium license?

[@LisaCrosbie]

You will need a premium license to be able to do this, yes.