Moving From Excel to New AI-Powered Tools

Microsoft Excel is a basic low-code tool, but data and business logic in an Excel spreadsheet are not managed and not necessarily shared with other business users, so they are not something that can be easily reproduced outside of that spreadsheet. : can be used. Microsoft’s Power Platform, the data platform used by Dataverse, is data-rich: it has metadata that tags business objects like email addresses, invoices and order numbers, with descriptions of what they should contain and what goes with them. Must do business, as well as support for business logic, authorization, intelligence, and analysis.

jump to:

Generative AI Power Apps Copilot can already be used to build applications in the Microsoft Dataverse by describing what the user wants from the app. For example, they may ask the copilot to add more screens, controls and features as they get more ideas.

There’s a new tool in Excel to App Preview that helps users bring in data that’s already in a spreadsheet. It does exactly what the name suggests: Users can drag and drop unstructured data from Excel – or give Copilot a link to the file – and Power Platform will analyze it, enriching it with additional information. , and will turn it into an app. , Nirav Shah, vice president of the Dataverse at Microsoft, explained to TechRepublic.

“Because it’s Power Apps Co-Pilot-enabled, it’s guessing what the table structure should be, how it should be named, what the details are, what columns are present and the data types of those columns,” Shah said. Said. “For enumerations (which are lists of possible values), it also automatically generates values ​​for the options you set in the dataverse schema.”

Giving Excel data a new home in the dataverse is great for data governance.

“Taking all that unmanaged and citizen data out there that is uncontrolled across the enterprise and turning it into a fully managed, structured cloud back end with full authorization policies, governance and security, tailored to the needs of the business at large.” Can scale, which can help reduce shadow IT. Enterprise,” Shah pointed out.

The new Elastic Tables in Dataverse can handle large amounts of non-relational data, ingesting up to millions of rows an hour.

Enterprises already use the tool to find “load-bearing” Excel spreadsheets on which business users depend. Now they can encourage them to bring that critical data into the dataverse where IT teams can back up, version and manage it, and other business users can take advantage of it. But, Shah suggested that individual users will also want to bring their Excel data into the Power Platform so that they can use the tools there — such as natural language — to build user interfaces for their apps.

“We think it’s going to take out a lot of the friction,” Shah said. “It provides a path forward for people doing personal productivity (working in Excel) to see the art of potential with the richness that the Dataverse in Power Platform can provide them.

“Dataverse is the native backend that is interconnected across the Power platform and is transitioning from Excel to all of the richness and capabilities we’ve found in the rest of the Power platform. The fact that you can do it in under a minute really removes the barriers for developers to start making the most of those capabilities within the dataverse on top of that data.

New AI-Powered Tools in the Dataverse

The data in Excel may be easy for users to work with individually, but bringing it into the dataverse connects it to a range of new AI tools.

Power Virtual Agent Chatbots

Once the data is in the dataverse, it’s available for Power Virtual Agents chatbots to use, including Teams bots that users can now create. If a user keeps a list of a company’s hardware assets like a projector in Excel and brings that into the dataverse instead, it could become part of an onboarding chatbot that helps new employees figure out what to do with official company HR tools. How to do things together.

Those bots can use Azure Open AI Service to start answering questions the bot’s original creator didn’t design them to handle. For example, if someone adds VR headsets and HoloLens to the hardware list, they can ask Copilot to include them in the app, and have the bot answer questions about them without the author having to manually add those details. Could

Team Toolkit for Visual Studio and Visual Studio Code

The Teams Toolkit for Visual Studio and Visual Studio Code make it easy for teams to build apps that use adaptive cards as the interface inside Teams. Along with the ChatGPT plugins Bing is standardizing for its AI Chat and Power Platform connectors, Teams messaging extensions that can be built with the Teams toolkit will serve as plugins for Microsoft 365 Copilot – Office applications and services. Upcoming AI tools that will have access to data from Power Platform stored in Dynamics 365 and Dataverse.

If you’ve wanted to do something specific for a long time, it might make sense to build an app in Power Apps to do it. Or once the data is in the dataverse, it can be as easy as asking Copilot to deliver status updates on the best sales opportunities or list the top trending customer issues from the past week. But users don’t have to choose because apps built using Power Apps Copilot have Copilot included, so they can ask Copilot to do things inside the app.

data sanitization tools

Now that it’s so easy for AI to use data in the dataverse, what Shah calls “data-driven applications,” it’s important to be clean, complete, and correct. This means that it should contain the complete details of the customers, no lines should be missed in the addresses and all the correct details on the invoice should be there. New AI-powered data hygiene tools in Dataverse perform de-duplication and smart data validation for objects such as email addresses and URLs, as well as physical addresses.

“Dataverse has a semantic data model that has a deep understanding of what the inherent value of the data is for emails and addresses because they are concrete data types, so it automatically adds a lot of richness in terms of data validation,” Shah said. can provide.”

Data cleaning and normalization is something business users may not think of doing, so having it built into the platform will help them get better results.

“We want to make it simpler and more turnkey for developers to get high quality data into systems so that the insights, the applications, the business processes can provide as much value as possible for the end users of the applications and for the developers to build upon.” are on top of the system,” Shah said.

Look: TechRepublic’s cheat sheet about data cleansing

How low-code developers can use Power Fx with Dataverse

Low-code developers can also use the PowerFX language to write their own custom validations for any immediate or on-demand actions – or to build business logic and other reusable plugins for the dataverse. Will be familiar to anyone who builds functions. Rules with triggers and actions that work with Power Platform connectors and Web APIs.

“It’s a low-code way to develop business logic and incorporate it into the system without having to go into full-blown .NET development,” Shah said. “You can trigger on specific records being created or updated within the system and then do what you want using PowerFX to call other APIs within the dataverse to interact with other data in the system or Invoke any of our over a thousand Power Platform connectors.(for other data sources) to orchestrate that logic or even create new APIs using PowerFX and then add them to those capabilities Exposed as objects that can be leveraged by anything built on top of the dataverse.

It can send an email to customers to thank them for their orders or replicate anything else users can do with an SQL stored procedure, but instead of requiring them to know how to program an SQL database, it can be accessed directly. Do it with Dataverse.

Users can already build rich custom business logic on events and functions in the dataverse, but this simplifies creation without a lot of development work.

“We’ve removed a lot of the barriers to entry and made it much easier and more accessible to use all the building blocks that are already within the system,” Shah continued. “It’s taking advantage of the context that we have within the environment and the data model to make it easier and faster for developers to add that business logic to the system.”

Using SQL with Dataverse

Dataverse is much more than a SQL database, but for developers who already know how to use SQL to write queries to explore, filter, aggregate, sort, join and group data Yes, they can use the new web-based SQL editor in Power Apps Studio to use their SQL queries against dataverse tables.

This is useful because it means that existing database developers don’t have to learn a whole new way of querying data, but the same technology is how different Microsoft Copilots can work with Dataverse data.

“Behind the scenes, what we’re doing is transforming the query from a logical representation that appears in the dataverse to physical storage through the metadata that we’ve got within the dataverse,” Shah explained. “It is also a critical component of how we support the many CoPilot scenarios being built on top of Dataverse.

“The ability for us to take natural language and translate it into a structured query that can run in the context of the user, along with their security and authorization rules that apply to them, enables Power App to answer those natural language queries CoPilot to be able, and other CoPilots in the Microsoft ecosystem are really, fundamentally, powered by our support for SQL queries on top of the dataverse.

Again, this helps experienced developers get to work faster, Shah suggested.

“Professional developers don’t have to build and put all those pieces together,” Shah said. “Because we have that understanding, because we have native connectivity across the broader Power Platform ecosystem, we’re able to automatically connect the dots, to create these app-specific co-pilot experiences in a turnkey fashion and Get that value out to your users more quickly, easily, than spending the time to build that scaffolding yourself.

Look: How to query multiple tables in SQL in this TechRepublic tutorial

Securing data with Microsoft Dataverse

With so much critical data in the dataverse, organizations may be looking for additional security options. If a user manages their own encryption keys in Azure Key Vault, they can now use this Bring Your Own Key option with Dataverse. They can limit access based on IP addresses in almost real time with a new IP firewall that lets the security team choose the IP ranges users can connect to.

If someone tries to take a sensitive action like deleting their account – which may be legitimate but may also suggest their account has been taken over by an attacker – take a look at the Azure Active Directory Persistent Access Assessment Enters how the account is authenticated and where it is connecting to. If a user goes home and goes to a different IP address or their machine appears as connecting from an unrecognized location, and it is not in the allowed IP range, their request will be blocked, even if they previously Were logged in from and usually allowed to do.

“Workforces are more remote and hybrid and are moving around the world in a way that they historically haven’t been,” Shah said. “If you don’t want users connecting from a coffee shop down the street, or you want to keep them within your corporate network, an IP firewall provides a mechanism, allowing people to secure and protect your infrastructure.” Provides another defense in depth capability for their most valuable asset, which is their data.

Policy for what users are allowed to do with that data may differ depending on which department they work for, and may now change depending on where they are working.