Recently, Microsoft introduced new feature Virtual tables creation wizard in Power Platform. With this feature, you can now create virtual tables directly from the SQL table, SharePoint and excel using a guided creation wizard. Virtual table enables to integrate data from external sources by representing the data as table in Dataverse without any data replication. Virtual tables allow CRUD operation unless there is any limitation from connection data sources.
You can create a virtual table for below external sources.
- SQL Server
- Microsoft SharePoint
In today’s article, we will only create virtual table for Sharepoint but rest details can be found on Microsoft learn.
What is virtual table/entity
Virtual tables enable the integration of data residing in external systems by seamlessly representing that data as tables in Microsoft Dataverse, without replication of data and often without custom coding.
Below diagram shows the overview of the virtual tables.
- Data Source – the location where the external data is stored.
- Data Provider – defines the behavior of the virtual table.
- Connection – this sets up the ability to connect to the data source and authentication.
- Connection reference – this provides a way for Dataverse to use the connection to the data source.
Benefits of Virtual tables
- The data from the external system can be configured and represented in Power Platform without writing any code
- Developers can write plugins to implement logic for virtual tables
- The user can see the data from the external system without logging in to the system.
- The data is accessed using metadata, so storage space is used in Dataverse
To create a virtual table, you must have a Microsoft Dataverse license through Power Apps or Microsoft Dynamics 365. Microsoft 365 or Teams licenses can’t be used to create virtual tables.
How to use new virtual table wizard?
Before we start, I created a sample list in SharePoint which shows Mens T20 team ranking. Below is the sample data.
The first step is to login to https://make.powerapps.com/ and select your environment
Click on Dataverse, select Tables. Click on + New table and select + New table from external data.
It will prompt you to select a connection or add a new connection to continue.
In this case, we will select SharePoint and click on Next.
Enter your SharePoint URL and click on Next
Here, you can see all list from your SharePoint site. We can select Mens T20 team ranking and click on Next
Now, you can see all your information from SharePoint list. You can change the details here.
Click Next to continue.
Here, you can review your details. If everything Ok, click on Finish to create virtual table in Dataverse.
This will take some time to create a virtual table in the Dataverse. Once this is finished, it will directly jump to tables.
Now, you can see below table is created in the dataverse. This is the same data which is the SharePoint list.
Now, you can use this virtual entity in Power apps or integration purpose.
Create a virtual table using Excel
Limitations and troubleshooting with virtual tables
When working with virtual entities, there are always some limitations.
Please refer to below link for detailed information.
The announcement can be find here.
If you like this article, feel free to share it with others who might find it helpful! If you have any questions, feel free to reach out to me.