Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
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.
Business Applications communities