Choosing The Right Data Source for Power Apps & Power Automate

One of the biggest challenges that we face when building apps and automations is the decision on where to store my data. Sometimes this choice may be dictated to us based on licensing and architectural factors, however, if you have a choice of options then this blog post is for you.

Think about the way that cars are advertised. Most car manufacturers have a super mini, family hatchback, cross-over, and SUV offering, and each one from an adoption perspective acts like a steppingstone towards the next model up next time as your wants and needs become more sophisticated! Great for us, but also great marketing for the supplier!

We all want the Audi RSQ8, but right now we might only be able to afford the Audi S1, or we might not want to commit the investment of the most expensive one right now. Note: Other car manufacturers are available of course!

Anyway, back to the technology, the most used data sources for Power Apps and Power Automate are usually Excel, SharePoint, Dataverse for Teams, and Dataverse, so let’s compare the options and understand how our needs can be met within Microsoft 365.

Microsoft Excel, the super-mini.

I have an ‘I 💖 Spreadsheets’ mug for my morning tea, and for some reason the world just can’t get enough of spreadsheets! Many organisations around the world are run on spreadsheets and nothing else. It was revolutionary at the time it was released.

A screenshot of Excel being used to create a shopping list.
A shopping list swiftly created via Excel.

Microsoft Excel is a hugely popular and fulfilling software tool, providing us with a quick way to format lists, calculate information, and visualise data. It’s formula functionality is so successful, that Power Fx, Microsoft’s language for developing Power Platform components, was inspired by it!

Excel is not a relational database though, and unless you have already defined a digital adoption strategy, spreadsheets are still saved locally on team member devices leading to a loss of business data over time.

When to use spreadsheets:

  • Quick lists
  • Personal recording of information
  • Extraction of data from another system into a universal format

When to seek one of the alternatives in this post:

  • When data is shared across multiple people or departments
  • When data repeats the same information multiple times, such as contact details

Lists (SharePoint), the hatchback.

Microsoft Lists has become a more prominent feature of SharePoint and has been rebranded as such to position the product as a feature primarily for use within Microsoft Teams. Lists combine the familiarity of Microsoft Excel, whilst also introducing concepts from relational databases and centralising of information to help increase quality by a significant proportion in comparison.

A screenshot of Microsoft Lists on the mobile and on a tablet device.
Microsoft Lists has the same features available regardless of what device you use.

Microsoft Lists can allow categorisation, links to users, and formatted fields with extraordinarily little effort. They won’t solve every problem, but they will help to keep sight of business data, and you can even generate apps and automation from Lists directly too.

Microsoft Lists is a well-received solution to our hybrid working scenarios where Microsoft Teams plays a huge part in operations.

When to use Lists:

  • Track information and progress within a team
  • Organise work and assign owners
  • Indirect benefit of preparing our business for modern cloud solutions that integrate across all of Microsoft 365

When to seek one of the alternatives in this post:

  • When sensitive data requires better security considerations
  • When your data needs to flow into another process in another system, or with another department

Dataverse for Teams, the crossover.

Following the release of Dataverse (previously the Common Data Service, or the on-premise Dynamics CRM SQL database to some of us older folk!), Microsoft also released Dataverse for Teams. This has been a fantastic middle-ground, offering organisations a step into the world of relational databases within the Power Platform, without having to initially commit to a licensing investment. The benefits of taking a relational database approach for this are huge.

A screenshot of creating a Power App in Teams, which will lead to the creation of a Dataverse for Teams environment.
Creating a Power App in Team will lead to the creation of a Dataverse for Teams environment.

There are some significant caveats in comparison to Dataverse, but you can set up your own database within a Microsoft Team, and build apps and automations on top of it, to service your end users. Remember, this is entirely free!

When to use Dataverse for Teams:

  • Small operational processes that require a team scope that can be defined within a Microsoft Team
  • When you need to build appetite for further Power Platform delivery in the future to demonstrate the art of the possible with little investment
  • When you plan to invest in Dataverse in the future, as the upgrade path from Dataverse for Teams to Dataverse is much more seamless than a migration project

When to seek Dataverse as an alternative:

  • When you need to retrieve data from sources outside of the Microsoft Team your Dataverse for Teams environment lives in
  • When you need to deliver Application Lifecycle Management (ALM) and utilise the concept of ‘development’ and ‘production’ for your solutions.
  • When you want to start utilising Dynamics 365 apps using the same database as your custom solutions.

Dataverse, the SUV.

We now look towards our final data source for review. I love describing Dataverse as the SUV. We see a nice car on the motorway with all the extras, we look up to it for inspiration on our next purchase, and one day we can finally make it to buy this dream car and it just works.

Dataverse is the same, it’s a full database offering with a comprehensive list of functions that require no expertise in SQL, just a theoretical understanding of relational databases and normalisation.

A screenshot of a Developer Dataverse environment.
A standard database environment configured for developer use.

Dataverse helps us to create a single source of the truth, and it helps us to share data from one record and relate it to others. Over time as multiple users build upon the quality of the data, you gain a significantly better understanding of how your business operates, which will help you to further improve your efficiency and services in the future. It’s worth the investment, and it’s worth setting up a free developer account to explore the possibilities if you haven’t already.

Conclusion

As we’ve discovered, there are so many tools at our disposal, even just within the Microsoft 365 stack when we’re delivering apps and automations.

My recommendation would always be to ‘climb down’ rather than ‘climb up’. We all know how easy it is to set up a spreadsheet and often we talk ourselves out of using another tool.

If we step back for a moment and consider our audience, our data model, and the impact across the organisation, it may be far better to rule out Dataverse first rather than having to justify its purpose 3-levels away from our currently proposed ‘easy’ solution which could cause maintenance issues in the future.

Modify An Owner’s Connection References in Power Automate

No matter how amazing an organisation may be, unfortunately there will always be the possibility of someone leaving the organisation. When it comes to Power Automate, this means that you can be stuck with the original Owner of the Cloud Flow having left the organisation, where Connection References eventually error, and lead to an automated process failing which may be critical to business systems.

Referenced Forever?!

At present, there is no way for you to delete the original Owner of a Cloud Flow even if you manage to establish yourself as a Co-Owner. Connection References cannot necessarily be deleted either!

Workaround

In this example I’ll use the Centre of Excellence Starter Kit environment that I inherited from a previous colleague, and for demo purposes I’m going to modify the Dataverse Legacy Connector as it is currently in the correct state to demo.

Let’s make our Connection References valid, and eventually fix the Cloud Flow by following the below steps for each Connection Reference:

A screenshot of a Power Platform environment, looking at Connection References within the Default Solution.
Your screen should look similar to the above screenshot at this stage.
  • Open the Connection Reference that you wish to modify. Hint: Filter by Owner to get to your reference quickly if you have many to search through.
  • Click on Edit.
  • Select the Dropdown with the existing Connection and re-point it to an existing valid Connection or create a new one.
  • Repeat those same steps for every invalid Connection Reference.

But Wait!…

Now there are some caveats to this approach which you should consider during this process:

  1. This does not remove the Owner from the flow, but it stops the Owner’s account from being used as a Connection Reference when using a data source in your Cloud flow.
  2. In my instructions I asked you to navigate to the Default Solution. For the consultants among us, with great power comes great responsibility. Be careful here, and use the original Unmanaged solution if you can. In most circumstances, you will be presented with Managed solutions and will be forced to use the Default solution.
  3. To ensure that you can see the full scope of your solution and automations, you ideally need to be a System Administrator to complete this exercise.

Find & Use Microsoft To Do For Your Personal Account in Power Automate

Way before Microsoft had a fully-fledged Outlook and Microsoft To Do app for iOS and Android, there were two apps that tightly integrated with each other to form an absolute machine in productivity – Sunrise and Wunderlist.

Tasks would show as ‘All Day’ items at the top of your calendar, with ticks next to each one completed as a frequent reminder of progress as you check your calendar for the seventeenth time during the working day.

A digitally produced image of Sunrise Calendar with Wunderlist Integration on an iPad
Sunrise Calendar with Wunderlist Integration on an iPad

Microsoft bought both of those products and that’s how we arrived at Microsoft’s eventual Outlook Tasks replacement and the ability to add third party calendars to our Outlook with ease, but not all features were migrated easily, and I have always wanted a replacement, but never found one.

By using the Power Platform, we now have the ability to bring together the capabilities of personal Microsoft To Do with Outlook, and any other service is hidden within the Outlook Tasks Connector within Power Automate!

Simply search for the Outlook Tasks when creating a flow, and once you’ve chosen your trigger or action, you’ll be able to see your tasks.

A screenshot showing the selection of a Microsoft To Do list in Power Automate via the Outlook Tasks Connector
Selecting a Microsoft To Do list in Power Automate via the Outlook Tasks Connector

I’m unsure on when exactly this feature became available for personal accounts, but Microsoft To Do with business accounts has been available for a while under it’s own Connector.

What’s the catch?

As with a lot of early Connectors that have since had iterative updates in Power Automate, not all actions are built consistently.

A screenshot showing a list of some of the available Actions within the Outlook Tasks Connector.
A list of some of the available Actions within the Outlook Tasks Connector.

We also have to bear in mind that Microsoft To Do and Outlook Tasks are built on entirely different architectures where functionality has merged over the years, and therefore there are several fields available that may not directly align to what you expect, particularly when trying to use the data you’ve received in another Connector.

Having said all of the above, once you have established the correct Dynamic Values and the correct Actions to use, the connector is extremely reliable and hasn’t failed me yet in any working examples.

References

Microsoft Docs: Outlook Tasks Connector

Microsoft Docs: Microsoft To Do (Business) Connector

Translating Unknowns into Tangible Requirements

For me, the most exciting part of a project is the challenge of figuring out exactly what a client is asking for based on a very short brief provided in an introductory call.

This challenge is increased in my industry when you move from Dynamics 365 based projects to pure Power Platform projects, because you move away from a functionally built system, to a set of tools that enable the capability. Not only do we now have to qualify the tool, but we also need to qualify the business process at an earlier stage than we typically used to, as well as the full data model.

For example, a “helpdesk replacement tool” screams Dynamics 365 Customer Service, and consultants in the industry typically understand the core operational processes before they speak to a customer. On the Power Platform, however, no two ‘self-serve chatbot’ projects would ever be the same, and there’s no functional process that you can align to this.

So how do we quantify projects with so many unknowns when we need to fully design the data model, the user interface, and the functional process? One way to start is to look for three themes:

  • Trends
  • Assumptions
  • Caveats

The first consideration I make is whether there are any repeatable components for any given high level requirement.

Whilst this doesn’t necessarily give us the full requirement ready to build, it does give us an idea of the size of the scope in contrast to a solution that is easier to estimate. Let’s take the idea of implementing a chat bot for a client on their website.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.

Within the industry I work in, we know that a configurable Power Virtual Agent for Teams solution that only uses Entities is relatively straight forward, and doesn’t require code. The interface used to build the solution is entirely controlled by Microsoft, so we also have confidence that it works! Let’s now put our original requirement into context by using known unknowns:

  • We know that the client cannot deploy this through Teams, but we don’t necessarily know exactly how to deploy it through a website that we don’t control just yet.
  • We are not being asked to build their website and we don’t know what their data source is, but we do know that we can take advantage of data and automation services that we can control to make this easier, perhaps Microsoft Dataverse with some sort of movement of data via Power Automate?

We now have broken down the requirement into tangible considerations and we can justify risk and complexity based on what we do know and what we can control, so we should factor this in to our estimate right from the beginning.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.

Trends:

1. Power Virtual Agents for intelligent chatbot functionality.

2. Power Automate to drive dynamic data interactions between end user and data source.

3. Dataverse to assist with controlling data where necessary.

Assumptions

Next up, assumptions. We are often taught that making assumptions is a bad thing, and in most cases that is correct, but assumptions can be extremely powerful when defining a requirement if used correctly.

Taking our earlier example of a chatbot being deployed via a client’s website, we really don’t want to be developing the website in unfamiliar territory, nor do we want run into any bumps if their data source isn’t fit for purpose. For now, we can set assumptions against our requirement to portray what we would typically expect within the client’s landscape, and if any of these are found not to be true, then we can justify a change in direction for a requirement through a change of scope, estimate, and change request!

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.


Assumptions:
1. Assumes that the client’s existing data model is fit for purpose, and if any changes should be made, the client will take responsibility for these.

2. Assumes that the solution can be deployed using a embedded HTML code snippet, as per Microsoft’s standard approach.

Caveats

And last but not least, we have caveats. Clients may see these as the supplier creating ‘get out of jail free’ cards, but in reality, these are to ensure that everyone involved understands what should happen in the event that one of these factors occurs. Caveats are usually based on assumptions, but can extend further than this to cover typical project factors too.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.


Caveats:
1. If the data source should change after delivery, the client will be responsible for a change request for any errors that may occur with this solution if they wish to continue using the functionality.

2. If the client’s website cannot support HTML snippets for any given reason, the project may need to be delivered via a Power Apps Portal, which would incur extra cost to ensure the delivery is built to the correct standard.

Summary

When I describe this way of working with my team, I reference a phrase that may be familiar to some – It’s about the journey, not the destination. Imagine you have a 100 mile journey to make with no map functionality, digital or print. What would be your first move?

Success isn’t just the destination, or the solution in this case, it’s the route to it and the service provided along the way that counts. This continues to be a significant theme throughout the whole lifecycle of the project, and it can make or break the final engagement with the software.