Connect to External Data with Dataverse Virtual Tables

In the previous post titled “Easily Import Data into Dataverse with Dataflows,” we walked through the process of importing data into Dataverse tables from an external data source. Dataflows are good way to import a copy of your data but there are scenarios where importing the data isn’t ideal. For instance, you might be working with a large amount of data and importing it would take too long or you may want to change the data and synchronize those changes back to the source which would require you to build a way to maintain that data integrity. Virtual tables solve this by allowing you to interact with the external data from within Dataverse without actually importing that data. The data will remain where it is at the source and will not impact Dataverse’s data storage (at least not in a meaningful way).

The Scenario

In this post, we are going to use an external data source of information, just like we did in the previous post. In the previous post, we imported data from a csv file containing Netflix data from Kaggle. That csv file was our external data source. In this post, we will use a SharePoint list as our “external” data source as it’s the simplest way to get started. An alternative would be to use a SQL table. Our SharePoint list will also contain Netflix data from the same source but with different filters. In the previous post, we imported a subset of movies and in this post, our list will contain TV Shows.

Staging our Demo Data

In the previous post, we used Power Query to query our data for a specific subset of movies from the Netflix csv data. We chose to display only certain types of movies from the US with PG-13 ratings. For this example, we’re going to import TV Shows into a SharePoint list. I’m not going to go step-by-step through the process of importing this information but you can easily go about it in 2 ways. You can go to your site’s Site Contents, click New > List. Then you can choose “From CSV”, as seen in the following screenshot, to Import the csv. It will start creating the list using the columns and data from the csv. So you can either 1) import the full csv, filter the list for Movies once it’s created, and delete the Movies from the list once all the records have been imported or 2) open the csv in Excel, delete all of the movies, then import the results. Option 2 would be the quicker of the 2 options.

Setting up a Virtual Table

The process of creating a virtual table is incredibly simple. We start by going to Power Apps and clicking Tables on the left. After navigating to the Tables page, look for “New table” in the top menu then click on “New table from external data” which will appear in the dropdown as seen in the next screenshot.

The first thing that you’ll be required to do is select or create a connection to your data source. As you can see, I have two existing connections already established that I can choose from. If you do not have any connections to select, simply click on New connection in the top menu, and create your SharePoint connection. Just be sure to that you’ve already created your list since we want to connect to existing data that lives outside of Dataverse. Once you’ve selected or created your connection, click the Next button.

After the connection has been selected, you will then provide the connection details. This is where you specify where your data resides. Since we are working with a SharePoint list, enter the URL to your site or choose one from the Recent sites list and click the Next button. Don’t enter the URL to the list itself, that will come in the next step.

If you’ve properly entered the URL to your site, you will a list of SharePoint lists that you can connect to. In my case, my list is named Netflix TV Show. I will select that item and click Next to review and finalize but before I do, you may have noticed a checkbox labeled “Configure table and column names that will be used in Dataverse.” If you uncheck that, you will allow Dataverse to automatically name your table and map your columns. While that may be fine, I suggest leaving this checked so that you can review this information and make any adjustments as necessary.

Since I left the checkbox checked, I am now able to see how Dataverse is going to be configured. For the purposes of this demo, I’m keeping the defaults; however, you can see in this example why it is a good idea to review this configuration before creating your table. Starting from the top, we see that the table is going to be called Netflix TV Show which is taken from the name of my SharePoint list. The title field was identified as the Primary field. Things may look a little odd in the field mappings. In my case, I have my external columns which contain fields like field_1, field_2, and so on. These are the internal names for my SharePoint list columns and they were automatically created when I imported my csv to a SharePoint list. Ideally, you would want to properly name your list columns so that the internal names match. That would make it easier to identify columns when building other solutions where the display name isn’t sufficient. Because the internal names are named this way, our Dataverse columns are also following the naming convention of the SharePoint list column internal names and you may want to rename them to something that matches closely to the display name to make it easier to work with. Again, for the purposes of this demo, I am going to leave this as is but you may want to change your field names. Click Next and we can do our final review.

In the review and finish step, you have an opportunity to review and go back and make changes but unless you see anything that sticks out, you can click Finish to see the final result.

The end result is a Dataverse table matching the configuration that we just reviewed but the data is not actually stored in Dataverse. The data seen in the following screenshot is the data as it appears in the SharePoint list but I can now manipulate it from Dataverse. Any edits to the items in the SharePoint list will appear here and vise versa. We can also build other solutions like a Power App that can work with this data, allowing us to build solutions using external sources while not having to worry about how you can get the right data out, how to sync the changes, and all the other complexities that come with integrating with line of business systems.

Conclusion

Well, there you have it. A simple way to pull in some data from an external data source that provides a 2 way sync without the complexities of importing, exporting, and change tracking. This approach provides a nearly seamless way to integrate external data in real time and if you are working with large data sets or want to avoid having to duplicate the data, Virtual Tables are a great option. If however you need to work with a copy of the data where you do not need to sync back to the source, or if the data doesn’t need to be current at all times, or even if you have to manipulate the data by filtering rows, removing columns, and massaging the format of other columns, dataflows may be a better option as it gives you the ability to work on that data prior to the import so that it’s in a format that works for your solution.

Do you have solutions that require integration with external data sources?