Equally a long-time CRM business analyst, I've used the D365 majority import wizard many, many times to import new and updated data into what is now Microsoft Dataverse. (I take given then many trainings to colleagues and clients that I could probably recite my Majority Import script verbatim.)

As CRM features go, it's fairly aboriginal, which translates into a multi-step, click-heavy procedure full of twists and wrong turns that tin pb users to inadvertent data loss through a VLOOKUP-gone-bad— or at least, a study full of import errors to sort through. And then, I've been patiently waiting for the adjacent-gen majority import tool to appear for Dataverse.

Little did I know, information technology is already here!

I stumbled across this feature past accident, and information technology'due south so damn useful that I'm kick myself for not knowing about it earlier! My quick search engine enquiry tells me that the Microsoft Power Apps Office add-in has been effectually since at least 2018 , only given how few write-ups I've found, I can't be the only i that missed its debut. The Ability Apps Part add-in traces its roots to the D365 Finance & Operations Excel data connector (it looks like just a rebrand of the Microsoft Dynamics Office add-in), and then I am pleased to meet more technology alignment and benefits coming from Microsoft's brand unification of the Dynamics 365 platform. A single data connector add-in for working across Dataverse and Unified Operations: yes, please!

If you also have nevertheless to uncover this lilliputian gem, then this post is for you!

Obsessed Aidy Bryant GIF by Saturday Night Live - Find & Share on GIPHY

I just love this footling add-in so much

get the Microsoft Ability Apps Role add-in

Getting this add-in is extremely straightforward, and once you install it, you can phone call it up in whatever Excel spreadsheet file.

  1. Open up Microsoft Excel.
  2. Get to the Insert tab.
  3. Click Get Add-ins.
  4. Click on the "CRM" category or search for "PowerApps" (no space… curiously searching for "Power Apps" does not yield the correct results here!)
  1. Click Add and agree to the license terms.

Voilà! Your new add-in is now installed, and can be chosen up in any workbook past clicking on "My Add-ins" on the Insert tab.

connecting to Dataverse

The Power Apps Office add-in volition appear as a right-hand bill of fare once you activate it from the Insert tab. Information technology doesn't look like the add-in has gotten updates in a bit, equally information technology yet references "Common Information Service" and links out only to Dynamics LCS and an outdated add-in details page, but make no error– this volition work perfectly with your Dataverse environments, no matter if they are Dynamics 365 CE, standalone Power Apps or even Dataverse for Teams databases.

If you choose, you tin can become a pre-packaged connectedness to Dataverse by downloading an export file from your model-driven app. This is similar to ye olde "Dynamic Worksheet" consign tool, which works by maintaining a web query connection string driven from the Data – Queries & Connections tab of Excel. Yet, since information technology's running through the add together-in, it's more than robust and, importantly, allows changes to be pushed back to Dataverse.

log into the add-in with Dataverse credentials

Regardless of whether you download a file with the connexion or first from scratch, y'all need to be signed into the add-in with the correct credentials of your Dataverse environment.

By default, you'll be signed into the add-in with the same AAD credentials that you're using for Excel. For many, this volition exist the correct credential prepare. However, if you need a higher level of access than your normal user account (like a service account), or maybe you're working cantankerous-tenant (say with a trial environment), you start demand to sign out of the add together-in and so sign back in to prevent error messages.

  1. Click on the person icon in the add-in ribbon and select "Sign Out".
  2. Follow the add-in prompts to sign in once more with the credentials you lot desire to use.

edit data in Excel with pre-made connection

  1. In the Maker portal, navigate to Data > Tables and select your table.
  2. Click the Data dropdown and select Edit data in Excel.
  3. Open up the Excel file from your downloads.
  4. Enable editing.

If you are signed in to the correct environment with the right level of access, the add together-in volition open in the correct-manus pane and refresh the data automatically. If not, follow the steps above to sign into the correct user account.

create a new connection

While downloading a pre-fabricated connection is perfectly fine, you should definitely learn how to create your ain connection and data file from scratch. This will salve you try each time you desire to manage the data, and give you lot finer control over the columns displayed.

  1. Click Add together connection.
  2. Click the Connectedness magnifying glass to automobile-discover environments.
    • ❗ Car-discover has not worked for me and throws an fault message. I'chiliad not sure if this is considering I'm using trial environments or if there's an issue with the add-in.
  3. If auto-discover does non work, change the CDS Endpoint from "Production" to "Direct Connect".
  4. Enter your base Dataverse URL into the Connexion field ("https:// [yourorgname] .crm.dynamics.com").
  5. Click OK and so click through whatever app reload warning prompts which appear.

Now you are authenticated to your Dataverse environment through the add-in! The next step is to design your table.

blueprint your data table

The add-in Designer allows yous to connect a Dataverse table and control the fields. For this case, I'll merely use Dataverse Accounts, only you tin can employ this add-in for pretty much whatever table. Including Portal Management tables.

The add-in will respect database-level restrictions, and there are a few field indicators to help remind yous which data you can and cannot affect via the connector tool. Hither are a few examples.

field options in the Designer
  • The central 🔑 icon indicates the chief key field (accountid).
    • You need to add this i in order to publish changes dorsum to Dataverse.
  • The red pencil ✏ icon (equally seen on accountid and _createdby_value) indicate a read-only field.
    • You tin add these to your view, but any changes you attempt to make volition exist discarded when publishing.
  • The star ✳ icon indicates required fields (name).

I'm non sure exactly what the red plus ➕ icon indicates. It tends to show up on read-only fields, but is also on the Condition (statecode) field, so likely indicates complex data types, or rollup/calculated fields.

Notice that yous can suit the column guild and even create a formula column. The formula column is interesting, because information technology cannot direct reference Dataverse schema names. For instance, I tried to create a simple formula to transform the Account Name into majuscule. Trying to utilize the Dataverse name yielded a formula error, merely putting in the Excel table reference worked just fine.

Formula Effect
=UPPER(name) ❌
=UPPER([@[Account Proper noun]]) ✔
a little trial and error to make up one's mind how the formula option can be used

The do good of creating your Excel formula as a column within the add-in Designer is that it becomes a function of the table design, rather than a manually-added cavalcade. As you lot refresh the information from Dataverse, the formula is automatically applied to new rows and recalculates any updated values. Neat!

how many tables can I add?

At first chroma, it appears equally if the add-in will allow you create multiple tables within the aforementioned workbook. You won't be able to add two tables on top of each other on the same canvas (it thankfully throws an error), simply it seems to let you add together tables wherever there are gratis cells, including on additional worksheet tabs.

Nevertheless, in practice, you can only have i connected table per workbook. Any additional tables you lot add volition be bare, even when no errors are thrown.

If y'all need to manage multiple tables through the Power Apps Office add-in, you'll demand to take these steps multiple times.

publishing changes

My favorite part of this feature is how piece of cake it is to push information updates back to Dataverse. The import process is lightning fast, and allows both existing updates and new rows to be pushed in the same action, bypassing the usual Majority Import Magician steps and the anxiety of refresh/refresh/refresh clicks watching it go through parsing and transforming and importing on the async service.

  1. Refresh your data tabular array through the add together-in to pull down the latest changes.
  2. Make updates to the data. Y'all can utilize all your Excel tricks for majority transformations and updates: find & supervene upon, copy & paste, VLOOKUP(), Alphabetize()/MATCH() and other formula-driven methods. Only make sure that the original columns refreshed from Dataverse take all formulas converted to values before y'all publish.
  3. Add new rows at the bottom of your tabular array.
    • Leave the Principal Key GUID blank
    • Add values for all required fields
  4. Delete entire rows to delete the record from Dataverse.
    • Don't just clear the cell values! You lot must highlight the row from the row selector on the left side of Excel and fully delete.
  5. Click Publish in the add together-in.
  6. Ostend the Create/Update/Delete numbers match the changes you intended to make to the table.

After the rapid publishing & refresh bike, y'all should now see your changes reflected in Dataverse and the GUIDs filled in for all new rows you added.

Y'all will be alerted if there are whatsoever errors during the process. You can besides review (and articulate) all previous errors, alerts and warnings inside the add-in by clicking on the small flag icon in the bottom correct corner.

what well-nigh Dataverse for Teams?

I recently started on my DV4T journey past working on a sheet app for my team to use. Previously, I'd only used either Dataverse Pro or SharePoint lists as data sources for canvas app projects, and I was curious to acquire more about Dataverse for Teams and where information technology might fit into my solutioning. (I'll save my Teams-based canvas app building experience for another 24-hour interval).

Ane thing that actually stymied me was the lack of data direction tools in DV4T that I am accepted to, coming from a CRM background as I do. I wanted a solution management experience for customizing the tables and views; I wanted a model-driven app experience to manage the data; and above all, I wanted BULK. TOOLS. FOR. IMPORT.

Perhaps the reality of my experience is due to how my organization has gear up up our DV4T permissions, but I felt blocked at every turn. Every "Edit data" button I could notice in the Teams Maker Portal opened up the simple in-line editor. I was not able to access my DV4T surround through the normal browser-based Maker Portal. I could not connect to the database straight; the "Switch to classic" ribbon selection sent me into an endless login loop.

[Update: I attempted this procedure for one of my DV4T environments today, and constitute that a UI update has removed this push. Leaving information technology up for posterity or those who have nevertheless to be updated. See below for the new navigation!] Finally, after clicking on every button and link I could find, I discovered this oddity of the Teams Maker Portal: in the Teams solution explorer, on a table, specifically on the Relationships subtab, there is a different ribbon push labeled Data. With an selection called "Edit information in Excel".

what a strange place to hide such a useful matter

This option downloads an Excel spreadsheet file with the Microsoft Ability Apps Part add-in connectedness pre-loaded in.

And then, yes, THIS As well WORKS FOR DATAVERSE FOR TEAMS. The process is identical once you lot know your DV4T database URL. You tin can find this URL when editing your sail app: click About and so Session details.

the URL will exist labeled "Example URL"

Update 01/xiii/2022

Seems like the DV4T UI is getting some updates. I was able to find the new method to edit the data in Excel after some earthworks around. It's all the same not very intuitive, and somehow the option is only bachelor in one specific location, so it's easy to miss if you lot're not as stubborn equally I am.

I also plant that getting the Case URL and manually creating the connexion will no longer yield successful results, at least in DV4T. None of my tables were showing up in the Design expanse any more after setting up the connection. This is disappointing, simply I suppose not unexpected given it felt similar a bit of a back-door to begin with. Endmost that door leaves us with downloading an Excel file with the connectedness and tabular array already pre-loaded.

Beginning, open up your DV4T environs in the Power Apps expanse of Teams, and go to the Build tab. Select your DV4T surroundings, and and then click the "See all" option to open the new Solution Explorer experience. Side by side, click on Tables in the left nav menu. Don't open the table you want to work with yet: you lot demand the list of all Tables, which is the but place I have found these options.

From here, select your desired Table and click the 3 dots menu (…). Here you volition find the Edit data in Excel option. This downloads the Excel file with the pre-loaded connection for this Table, and from here works the same equally this original blog postal service.

conclusion

The Microsoft Power Apps Office add-in is an unsung hero of the Dataverse earth! I hope that Microsoft continues to improve upon and advertise it as an first-class data tool for app makers and pro developers across the entirety of the Dataverse and Dynamics 365 suite.

For 1-time bulk import needs, it's a quick and piece of cake way to build a tailored upload to handle all create, update and delete actions at the same time.

For on-going bulk import processes, the file can be saved either locally, to OneDrive for Concern or within a Teams/SharePoint repository along with the configured connection, tabular array blueprint and formulas for a repeatable data direction process with extremely minimal echo setup time.

For Dataverse for Teams apps, it'south a killer mode to get access to, initially seed and manage your data sets without needing to perform row-by-row data entry. [Update: so long every bit Microsoft doesn't keep moving the buttons to admission the options]

Take you used this add together-in before? Let me know in the comments if you have any great experience to add!