Load data from external data sources to the Common Data Service using Power Query (Previous Version)
We’re very glad to announce initial Preview availability of a new feature that allows users to create custom entities based on data from external data sources using an embedded Power Query experience in the PowerApps portal. Power Query is a feature that enables data connectivity and data preparation across many Microsoft products and services, including Power BI, Excel and Analysis Services.
The Common Data Service allows users to securely store and manage data within a set of standard and custom entities. An entity is a set of fields used to store data similarly to a table within a database. After the data is stored in the Common Data Service users can use Microsoft PowerApps to build rich applications using this data.
Business Apps Developers using PowerApps can now use the Power Query feature to create new entities and then load data into the Common Data Service based on data from a wide range of external data sources, including on-prem data sources such as relational databases (SQL Server, IBM DB2, etc.), Excel, Access or Text files, Online Services such as Salesforce, Azure SQL Database and Data Warehouse, Web APIs, OData feeds and many more sources. In addition to a wide range of data sources, Power Query also provides market-leading capabilities to filter, transform and combine data before loading it to the Common Data Service.
Once in the PowerApps portal, you can browse to the “Common Data Service -> Entities” tab in the left navigation pane. From the Entities list, you will see a “New Entity” dropdown menu, under which you will find the “New Entity from Data (Technical Preview)” entry.
After clicking this button, you will see a dialog displaying the list of available connectors in this initial integration. Note that you need to trigger this flow from a database that you created[DZ(1] , in order to be able to create new custom entities.
Once you have selected the desired connector to use, you may continue to the next steps to specify data source connection details and credentials, select which tables to import, etc. You may be able to access the “Query Editor” as well (via the Edit button in the Navigator view) in order to apply filters or data transformation steps before importing data into CDS.
Once you are done preparing your data for loading into CDS, you will be able to adjust additional Load Settings, including Refresh behavior and entity-specific settings (i.e., Display Name, Primary Keys).
After completing these steps, you will have created new custom entities in CDS. Note that you can “Edit Queries” after the initial load by accessing the Entity menu for a specific entity.
We’re very excited about this initial integration becoming available to customers and going forward will continue adding new capabilities (data connectors, data transformations, etc.) incrementally based on your feedback. Please do not hesitate to contact us with any comments or suggestions regarding this preview, by posting a comment on this blog post or in the PowerApps Community.