Tag: power apps

Data Importer Tool for PPTB – I did a thing!

If you’re not aware of the ‘I did a thing’ quote origin, it’s Jeremy Clarkson’s catchphrase from Clarkson’s Farm. Which I’ve been watching religiously for the last few years – it’s an amazing eye opener into the reality of farming (from a UK standpoint, though has wider principles too). When I decided to create my own PPTB tool, I just couldn’t resist using this as part of the opening. Whether the tool results are better than Jeremy’s farming attempts remains to be seen… 

This is the story of why I built a tool I’d never have dreamed of building a couple of years ago, what it does, and why I think you should go try it out and use it.

The problem/challenge

If you work with Dynamics 365 or Power Platform (specifically data in Dataverse), you’ll know this one. Someone hands you a spreadsheet, and it needs to be in Dataverse. Properly – with with lookups resolved and choices mapped, and ideally without losing a week (or more) of your life.

I’ve tried the usual suspects over the years, and each one fell short for me in some way: 

  • The data import wizard – no real way to preview your data before it goes in. You find out what went wrong afterwards. 
  • Excel import – the same sort of gaps, and fiddly when you need real control. 
  • Dataflows – yes, Power Query gives you a preview, but for what is often a fairly straightforward job it’s a lot more difficult than it needs to be. 
  • Lookups – I had serious challenges getting these set, particularly where alternate keys were involved. 
  • Option sets – where the name of the value wasn’t the actual value (for reasons I’m still not entirely sure about), it just errored. Constantly. I’m not sure what Microsoft holds against these – maybe because I decided against using SSIS?
  • SSIS – it would do the job, but that’s a sledgehammer, usually used for very complicated and large datasets. When you have flat files to import, surely it doesn’t need the full power of this.

I also couldn’t pause an import in any of the existing tooling I used. When you’re a long way into a big load and something looks wrong, that matters. And on top of all that, I wanted proper, verbose logging that was easy to read (if you’ve ever taken a look at the errors that the native import tool gives, you’ll know EXACTLY what I’m talking about). There was nothing on the market that did what I needed, so I decided to build it. 

Where the inspiration came from 

Well, I didn’t just decide to build it. I’ve been a major fan & user of XrmToolBox (XTB) for a number of years. The commitment by people in the community to build tools on their own time, for free, is incredibly amazing and inspiring. I’ve used many of the XTB tools over the years, so when I looked to see what could be done to solve my frustrations with data imports, my first port of call was to go look at what might be available in XTB.

Now there is a data import tool there, called ‘DataImport++‘. It’s still in use by people, and I went to try it out, but it wasn’t giving me all that I was looking for. I contacted the people who developed/support it, who said that it was mostly in support mode now, with no major releases being planned for it, but that I was welcome to fork & submit ideas for it.

I tool a look at doing this, but found that there were potentially limitations with what I was looking to do. Additionally, with the emergence of the Power Platform ToolBox (PPTB) on the scene, I was looking to see if I could do something with a much more modern influence, look & feel.

PPTB is a desktop application where you can discover, install and run community-built tools in one place, and it openly honours XrmToolBox as its foundation. It felt like the right place to see what I could do. I didn’t just look to copy & reskin DataImport ++ – my development went down a different pathway, with some different functionality. However, I want to give full credit to the developers for Data Import++ for the inspiration to create my tool – it simply wouldn’t exist without the idea that came before it. 

Giving something back 

Our community runs on people sharing what they’ve built. Every one of us has used a free tool, a blog post, a snippet or a workaround that someone else took the time to share, and it’s saved us hours. I’ve benefited from that for years, and I wanted to give something back. I’ve obviously been blogging for a while now (along with other community engagements), but always wanted to be able to be part of an effort to create a tool for the community.

Here’s the honest bit: I’m not a developer. Building something like this by hand would have been totally beyond my capabilities. What made it possible was AI. I used VS Code with GitHub Copilot, and combined it with two things I did bring to the table: knowing how to prompt it well, and knowing the scenario I wanted to build for inside out, because I’d lived with it on real client projects. 

That combination is what got me from “this should exist” to “this exists”. I’m genuinely grateful that this is now possible. Ten years ago, an idea like this would have stayed an idea, or a long wish-list item for someone else to build. If you’ve got a problem you understand deeply and have been told “that’s a developer thing”, my advice is to have a go, especially given what we’re able to do nowadays without coding ourselves!

What it does 

At its heart it’s simple: pick a CSV or Excel file, pick a Dataverse table, map your columns, and import. The detail is where it earns its keep: 

  • CSV and Excel (.xlsx/.xls) – choose your file and worksheet, and headers are detected automatically. 
  • Column-first mapping – the grid lists the Dataverse columns and you pick the source column for each. Columnsare auto-matched by logical and display name, so most of the donkey work is done for you. The reason for doing it this way (at least for the moment) is because I’ve seen scenarios where source values need to be mapped to multiple destination columns. I’m open to changing the way this works, based on feedback being received!
  • All the column types – text, numbers, currency, dates, GUIDs, Yes/No, choices, status/status reason, multi-select, lookups and owner. There’s even a Created On override. 
  • Choice mapping – confirm how each distinct value in your spreadsheet maps to a Dataverse option (local or global), with green/red badges so you can see at a glance what still needs attention. This one fixes my option set pain. 
  • Lookup resolution – by GUID, or by a column value queried in Dataverse. Polymorphic lookups are supported, and you don’t need an alternate key to match records. 
  • Preview – see what’s going in before it goes in. 
  • Create, update or upsert – matching on one or more columns. 
  • Verbose logging – live progress and a per-row failure report. It carries on past failures, so one dodgy row doesn’t wreck the lot. 
  • Pause the import – mid-run, whenever you need to. 
  • Save and load configurations – export your table, settings and mappings to XML and reload them later. If you do the same import across environments, this is a lifesaver. 
  • Light and dark themes – because we’re not savages. 

Speed 

This is very important and relevant. If you’ve ever done data imports at scale with the import wized, you’ll be more thna aware that not only are you looking at taking a coffee break, you likely need a breakfast & lunch break too!

The tool uses batching, with a configurable batch size, to speed things up. As an example, I ran it against a very large data set of 100,000 rows, and it took around 25 minutes. From experience, the out-of-the-box import takes a lot longer to get through that kind of volume. I know the pain, because I’ve sat through it more than once, watching a progress bar and questioning my life choices. 

Is this a conceptual tool?

Not at ALL!! I created it due to staring at needing to import circa 500K rows of data for a client solution – and having the standard import error, error & error on me I’ve now used it on more than 10 client engagements, and have colleagures using it as well.

It has now passed 100 downloads (which isn’t a major milestone, but has indeed prompted me to actually write this blog post!). For something that started life as “I just need this to work on my project”, I’m chuffed. Thank you to everyone who has installed it. 

FYI, it seems that some organisations restrict PPTB. If that’s you, I’d suggest working with your IT and governance teams to get it unblocked. It’s an extremely valuable tool, not just for Data Importer but for the whole ecosystem of community tools that now sit inside it. 

Go and try it 

You can find Data Importer in the PPTB tool marketplace here: Data Importer on PPTB. 

The code lives on GitHub, and that’s the place for feedback. If you find a bug, or you’d like improvements or extra functionality, please raise it there as an issue or feature request. This tool gets better when the people using it tell me what they need, and I’d love to hear what you’d like to see. 

Go and give it a spin, and let me know what you think. And if it all goes wrong, well, at least it’s not a barn full of cows!

Changes in the FTRSA Program

Firstly for those who are not aware, the acronym ‘FTRSA’ stands for ‘Fast Track Recognised Solution Architect’. This is an award that Microsoft bestows on people working for Microsoft Partners who have demonstrated clear technical expertise & understanding of the Microsoft Business Applications Platform at (enterprise) scale.

To quote from the Microsoft documentation for the program:

The FTRSA designation is awarded by Microsoft’s Business Industry & Copilot (BIC) engineering team to enterprise solution architects who exhibit outstanding expertise in architecture and deliver high-quality solutions. Recipients are typically nominated based on their exceptional skills, extensive experience with Microsoft products, relevant certifications, and leadership in projects.

The award covers two main areas – Power Platform & Dynamics 365, with different capabilities under each area.

The program has been around for 6 years now (since 2019), with people needing to submit for annual (re)award & recognition. On average, approx. 120 people are recognised with this award globally. It is definitely something that Microsoft Partners can place a large emphasis on if they have people with this!

Generally over the last few years, the categories for being awarded have included:

  • Power Apps
  • Power Automate
  • Power BI
  • Dynamics 365 (CE)
  • Dynamics 365 (ERP)

Changes over the last few years have included the Power BI category being retired. This is to be expected, I guess, given that Microsoft programs tend to flex/pivot over time.

The process for application is simple. By this, I mean that nominees need to fill in a form (located at https://aka.ms/FTRSANomination). In this form, they then need to provide various pieces of information, such as their personal information, the partner that they work for (including the Microsoft Partner ID), as well as submitting proofs to show that they currently fulfil the necessary requirements for the program. These requirements can vary based on the technology, and over the last few years I’ve seen a few different versions (based on the year).

The form is usually open for around 3 months or so, opening at some point in October, and closing at some point in January.

Once submitted, the information is then sent to the relevant Microsoft team who oversee & run the program for review. There are several stages to the review that is carried out:

  1. The team carry out an initial review of the information provided, ensuring that it meets the program requirements. Applicants who have not provided the information to meet the program requirements/criteria, or who do not pass the initial review threshold as evaluated by the team (this is why applicants are recommended to ensure that they’re focusing on quality of information being submitted), are not progressed and are notified.
  2. Applicants who pass the first stage are then invited to an interview. This is carried out with one of the wider team members, based on region & availability. The interview usually lasts around one hour, and is an evaluation of the technical skills & expertise of the applicant. During this interview, candidates are required to present on a project that they have implemented, and to demonstrate their in-depth knowledge & role that they played on the project.
  3. Finally, the team reviews the interviews, and decides as to which applicants have successfully shown their skills & expertise. Applications who have not met the level required are notified, along with feedback and areas that they could look to work on for a future nomination.
  4. Successful applicants are notified as well directly, though the news is not publicised until May or so, when the public announcement takes place with the relevant FTRSA websites being updated with their information.

Business Contributions

Having taken a look at the nomination form for this year, there are some new changes coming in that will be quite important (in my opinion) to pay attention to. These are being referred to as ‘Business Contributions’. Specifically, applicants will not only need to demonstrate technical/project expertise, but will also need to demonstrate one or more business contributions.

Depending on the technical area being selected for the application (Power Apps or Dynamics 365), these are the areas that contributions can be submitted for:

Power Apps

  • Published Microsoft Customer Stories or Microsoft Partner Stories, or evidence of nomination to be published
  • Contribution of product feedback to engineering teams, advisory boards, focus groups, communication forms or private preview programs
  • Published technical samples (e.g. code snippets, data migration templates, integration samples, etc) in the PowerCAT GitHub channel
  • Proof of escalation reduction in customer implementations
  • Reference architecture article/s used with a customer that leverages the Power Platform Well Architected framework

Dynamics 365

  • Onboarded customer implement project(s) in the Dynamics 365 implementation portal, leveraging Dynamics 365 guidance hub frameworks
  • Published Microsoft Customer Stories or Microsoft Partner Stories, or evidence of nomination to be published
  • Contribution of product feedback to engineering teams, advisory boards, focus groups, communication forms or private preview programs
  • Published technical samples (e.g. code snippets, data migration templates, integration samples, etc) in the Dynamics 365 guidance hub
  • Published contributions to the Business Process Guide Catalogue
  • Proof of escalation reduction in customer implementations (either partner led or FastTrack led implementation)
  • Submit additional reference architecture articles for review and potential publication

This is a significant change for the program – for the last 6 years, it’s been purely expertise recognised from client engagements. Now (in the 7th year, and I’d think very likely going forward), people considering nominating for FTRSA will need to prove that they’re giving back to Microsoft in some way, other than just running client engagements.

Overall, I think this is an interesting concept, and generally a good one. Let’s face it – being able to talk about technology (at scale) is something quite a few people can do, but it doesn’t meant that they’re necessarily good at it. I know of several over-architected projects that I was brought in on, where just because lots of technology components were used, didn’t mean it was doing well. Part of the skillset as an experienced/knowledgeable architect is also when less is more!

Additionally, being technically competent is of course important, but personally I believe that being able to be clear & communicative is also a very important role for a solution architect. Essentially having that functional view, as well as being able to engage appropriately with customers (as the owner of the project) is vital as well. One of the

I also think that Microsoft is wanting to see that the program in which they’re investing time, effort & resources (yes, FTRSA’s get a wonderful SWAG box – THANK YOU TEAM!) are providing ROI back into Microsoft in terms of feedback, input & other information. This way products can (hopefully!) get better, visions can be assisted with customer information, and others can be helped as well.

Some people may say that this is becoming more like the Microsoft MVP program. Given how much MVP’s are required to do, in terms of community (& Microsoft) engagement, I can understand the thoughts, but really don’t think that it’s anything anywhere near to that. My only note on this would be that I hope that contributions remain business/technical focused, which to me seems in line with the stated goals of the program, rather then also include (other) community contributions.

Of course, there are those people who may choose not to do such things, and just focus on the project/s that they’re working on. This is a valid scenario, and there is of course absolutely NOTHING wrong with this. Not all of us may wish to engage with Microsoft engineering teams, or provide information publicly. And that’s all fine. However I would politely point out that nothing remains static, and if you’re wanting to receive (or continue to receive) the FTRSA award, you may need to do some thinking around how you’re approaching it, with the change that’s come this year.

I’d also encourage people who are considering applying for the FTRSA award recognition to reach out to an existing FTRSA, who could possibly help mentor, review & guide you. They’ve already been through the process and are recognised as such, and therefore have a pretty good idea of what ‘hits the bar’ and what may not.

So if you’re thinking of going for it – I wish you the best of luck!