Create easier data connections in Power Apps with virtual tables

Low code isn’t new. For decades, business users have turned to Microsoft Access databases and Microsoft Excel macros to create their own tools, but those solutions had to work with exported data that didn’t stay in sync with enterprise data sources.

That also meant relying on a database administrator to provide access and run complex ETL jobs — likely with manual coding to process the data that can introduce inconsistencies. Neither the low code apps nor the data they used were managed, secured or guaranteed to be up to date.

SEE: Hiring kit: Back-end Developer (TechRepublic Premium)

One of the reasons Power BI has become so popular is its large ecosystem of connectors can bring in data from thousands of services and data stores so you can analyze and visualize it. Useful as those connectors are, not all no-code and low-code users in Power Apps and Power Automate have the query writing skills to build dataflows to deal with transforming internal data this way.

That’s so much of a barrier that Microsoft is using AI code generating tools to help. Even keeping data you bring into Dataverse from an Excel spreadsheet into Microsoft Dataverse in sync with the original involves more work. 

Jump to:

A richer way to handle data

There’s a lot of business data that would be useful to work with in Dataverse, which Nirav Shah, vice president of Microsoft Dataverse, described as a data platform rather than just another SQL data store.

“Yes, it starts with data storage at the bottom, but it has a really rich runtime and infrastructure that supports business logic, authorization, intelligence and analytics, as well as integration into a broader Microsoft platform,” he said.

Dataverse is the data backend for the Power Platform, but it’s also what the whole Dynamics 365 suite of applications is built on.

“It’s a full stack infrastructure built to enable folks to focus on solving business problems through integrated intelligence and systems of action to simplify scaffolding different services and infrastructure together to solve those business problems,” Shah said. “We’ve taken all of the scaffolding required to build complex, rich, enterprise grade applications, packaged that together into a no code structure within Dataverse, using that rich metadata and understanding of the data model. We’re then simply able to relate all these different components together.”

With tools like AI Builder, it provides security and governance, application and data lifecycle management, business logic, and intelligence and analytics.

Dataverse used to be called the Common Data Service, because it’s an implementation of Microsoft’s Common Data Model. That model includes rich metadata about common business objects — like knowing that invoices have due dates, currency amounts and addresses, that customers have account numbers and credit limits, and that phone numbers are something you can call.

This intelligence about how data fits into your business processes is something you want to apply to data coming from other sources as well as to data you only store in Dataverse directly, without needing business users to have developer expertise and without having to abandon or rebuild all the enterprise applications you’re already using.

“There’s lots of data that already exists across the enterprise, and folks need to be able to integrate and leverage that as part of their business processes: It doesn’t start and end when you start building on the Power Platform,” Shah said. “What we often see is customers duplicating data through complex ETL processes, and that has all sorts of problems — delay from synchronization problems or inconsistencies. That results in high costs to maintain, manage and operate those processes, because you’re got multiple copies of the same data that you’re trying to keep in sync.”

Virtualizing data

To integrate that existing business data with new data that lives in Dataverse without losing the context of the original data stores and applications, you can use virtual tables. Rather than replicating business data by exporting it through a file or connector and keeping a copy in Dataverse, virtual tables let you connect directly to the data source at runtime so you always have a live copy of the most up to date data. Any changes you make are reflected in the data source straight away, where legacy apps will pick them up too.

You can make a Dataverse table of data from an Excel workbook in OneDrive or SQL Server in Azure to use in a Power Automate workflow or in Power Pages, and you can use that business data in a model-driven Power App that only understands Dataverse data.

“You’ve got this really complex critical data that you need to use for more business processes as you digitally transform and optimize,” Shah said. “What we’ve enabled with virtual tables is the ability to start capitalizing on that data in place, while modernizing and bringing it to the Power platform. It can spread across the enterprise in a very controlled way and have an evolution towards something that’s more cloud native.”

With virtual tables, you get to use the existing data in new no-code and low-code apps, so what used to be siloed data becomes now part of more business processes. That data itself also gets richer because bringing it into Dataverse turns it into entities.

“All that data that was quarantined off in existing systems,” Shah said. “Because it’s now integrated in Dataverse, it has business logic and APIs naturally associated with that data in a consistent shape and format with that business logic based on the business events associated with that data as it flows through the system.”

Like the rest of Microsoft 365, Power Platform has Azure Active Directory authorization and authentication built in, so you can use Dataverse to secure access to that data using role-based permissions.

“All of a sudden, we have visibility into siloes and how they interact with the broader business processes,” Shah noted. “All of a sudden, you can say: ‘You know what, A has access to this data, but B doesn’t.”

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

Simplifying virtual tables

Virtual tables work like any other table of data in Dataverse, but creating them is complicated. A new wizard in Power Apps makes it far simpler to bring in data from SharePoint or SQL databases — which could include Access databases as well as SQL Server — without writing a lot of code or doing a lot of configuration.

When you must create a new table, choose New Table From External Data and pick New Connection to choose the data source. Then, sign in with your usual credentials.

You will also need the URL of the specific SharePoint site so you can pick the list that has the data you want in. If the names in the data source aren’t very helpful, you can change what the table will be called in Dataverse and select the primary field to use.

Now you can open the Dataverse table where you want to use data from the virtual table: Create a new column that’s a Lookup and pick the virtual table. When you select and edit the SharePoint data inside Dataverse — including in a Power App — changes will be synced back to SharePoint (Figure A).

Figure A

There’s also a virtual connector for working with data in Excel, which isn’t as simple as the wizard but does more of the work for you, like mapping the primary key.

Other Microsoft 365 services that already integrate with Dataverse and the Power Platform do that using the same virtual tables infrastructure, but they’ve built richer custom integrations than the new wizard. Microsoft will take feedback from customers for other data sources enterprises want to see in the wizard, but developers can also create their own virtual table providers using custom plugins or the OData data provider.

Don’t worry about virtual tables taking up space in Dataverse: What’s stored is just the metadata for the connection, not a copy of the data source.