Using Data Integration in Microsoft PowerApps

Isaac Stephens, 21 May 2019

Storing data in a CDS entity is a useful way to have it accessible to your PowerApps applications. This is because rather than embedding the data source directly into each of your applications you can connect multiple apps to one source. Another benefit of using CDS is that you can create a Data Integration that connects the entity to an external file. This allows the entity to be updated with new data when data in the connected file changes, this can be useful when dealing with big data sets and/or ones that change on a regular basis.

In this blog I will show you how to connect an Excel file stored in OneDrive/SharePoint to a custom entity and then either set this up to automatically update on a set schedule or to update on a press of a button.

To start with I created a custom entity called ‘Fruit’ which has the fields; Name, Colour, and Type and created a matching Excel file with data.

Once you have these you can now connect them together, to do this open the entity and click ‘Get Data’ and then ‘Excel’, click on ‘Browse’ and find the file inside your OneDrive/SharePoint.

Once the file has loaded you can now start using Power Query to select the exact data you want. This is useful in large data sets where you may only require certain Excel Sheets, Columns, rows, or filtered data. Its also very easy to removed empty rows. For this example, select the Sheet you made, in this case Sheet1, and then select reduce rows and ‘Remove top rows’. Type in 1 and this will remove the column names ‘Name’, ‘Colour’ and ‘Type’. Now you will only have the data and can click next at the bottom of the screen.

Mapping the Columns to the Destination field is important to get right to ensure the correct data is in the correct fields. Refer back to your Excel file to see what columns your data is in, mine is as show below.

After clicking next you will have the option to schedule automatic updates. This can be incredible useful to keep all your data current. If, for example, you always get your new data list at the start of the month then you can set the schedule to trigger each month. However, if you have irregular updates or need the data to be updated as soon as you upload the new file then you can set it to manual, this means you will need to click refresh each time you update it but won’t have to remap or reconnect it.

Once you have selected your update option the Data Integration will start and once complete you will have an entity full of your data from the excel spreadsheet.