For those using Dynamics 365 F&O (formerly Dynamics AX), this is a feature that was extensively used when creating customizations. You create a new entity/table, don’t necessarily have a user interface for it, but you need to check the data, input and edit data, or simply validate and audit totals from data imports or integrations.
You’ll be familiar with the ability to take that data set into Excel, and work from there. Excel requires an add-in for this.
Now, with the evolution of the Power Platform, this very useful feature has made it’s way into our toolset. Big round of applause for that!
How can we use the Edit data in Excel option
Let’s start from the Maker portal in Power Apps. Navigate to a solution, and select one of the entities you want to edit data. This is a bulk edit without the need for a data re-import/update.
Select the Data tab on the respective entity. You can choose the view to display from the top-right drop-down. I have in this case selected the All Visits view, which is a custom view that adds a whole bunch of fields from my Visits entity. The below screenshot show this.
Observe on the ribbon the option to Edit data in Excel. Let’s click on it.
You are prompted for a local folder to save the file. Go ahead and select where you want this file saved. I’m just choosing Desktop for simplicity, but if you have a local project folder, you will want to drop it there.
Once the file is downloaded, click to open it. This opens up Excel, and you are prompted to Enable Editing. Do that.
Once the file opens, you will observe on the right side the Microsoft PowerApps Office Add-in. You will be prompted to login with the user having access to the instance you started from.
As soon as you’ve logged in, a data pull takes place, and the grid view gets populated with the records your user has access to see. Note that security trimming is in place.
Good, now we can start editing this, or validating totals, or anything we used to do in Excel normally. But wait, there’s more!
Observe the options presented on the bottom of the plugin, like in the screenshot below:
New, as expected, adds a new record to the data set. Kind of redundant, as you can simply scroll to the next available row and add a new record that way.
Refresh, as expected, triggers a new pull from CDS and refreshes the data. This is useful when you have the file saved on your desktop and work with it over the course of several days, or you use it for weekly/monthly/etc updates. For example, I’ve been using this approach to check totals for monthly data loads from an external service in order to audit the integration process.
Publish allows you to push the updates you’ve manually made in the current data set back to CDS. This is the equivalent of re-importing a data set that was previously exported for re-import. Now it’s just a one step process.
Finally, the Filter option is where the value is. In the scenario described above, I’ve used the Filter option to select a sub-set of the data set exported that was created during the course of a specific month, and calculate the total only for the respective month.
You can of course combine a number of filters, as shown below:
You will quickly see that the conditional options are not as comprehensive as an actual Advanced Find or other places where we’re used to doing this. The only available options are described in the screenshot below.
You will not find options around:
- Contains data
- Does not contain data
Moving back to the top of the Add-in, next to the logged-in user you have a cog icon. Clicking on it opens up a set of connection and source options. There are two main sections
- App
- Data Connector
In the Data Connector, you have a few options to configure the behavior around data synchronization and confirmation.
The beauty is that, once you configured your filters, you can actually share this file with other users. They will login with their own credentials, applying security trimming, but will leverage the filters already created, and possibly other functionality you have decided to build in this file. Just like working with any other excel file, you can create all kinds of complex formulas or charts based on this data. These would be all shared with the team you’re sharing the file with.
In conclusion, a feature that’s been available for a while in Dynamics 365 F&O/AX now makes it to the Power Platform, and it’s a welcome addition to the current tool set.
How do you see yourself using this in the future? Leave a not below!
thatcrm.com 
Leave a Reply