How to use Power Automate to Import & Deduplicate data from Excel Online to the Dataverse

 We know that importing contact records from an Excel file to the Dataverse is fairly easy, there are several tools available, however, have you tried to import records from a Excel Online file using Flow? I recently needed to do this and to my surprise, it was not as simple as I originally thought.

In this post we will review some of tools and functions I used to create the final solution.

• How to use the Power Automate Search records action (Currently in preview)
• How to use the empty() expression
• How to use a condition to check if an array is empty
• How to import records from an Excel Online file using Power Automate

Recently, I needed to import contact records that were in a OneDrive folder, however, about 50% of the contacts had already been incorrectly uploaded, so I need to not only import the records but check for duplicates and fix the existing records.

In short, I needed to create a Flow that will search for a contact record in the Dataverse using an email address, if the record is found, clear out the first name, update the last name and set the contact type. If the contact was not found, then create the contact record.

How hard can this possibly be? Well, it was not as straight forward as I thought.

How to deduplicate and import records from Excel Online using Power Automate (Flow)

Before we jump right into how to create the Flow, lets evaluate the requirements.

Current Application

• Dynamics 365 Marketing

Requirements

• Import a list of contacts from an Excel Online file
• 50% of the contacts have already been imported but in error, their first name and email address is the same value, this must be corrected.
• For any contacts that have not be imported, import them.
• Update all the contacts to have a contact type of Account.
• Create a segment in Dynamics 365 Marketing, for all contacts of type Account.

Some challenges I faced while creating the flow

• In order to use the Get row by Id action, I needed the Dataverse record ID, so that rules out using this action.

• I tried using the List Rows action and filtered with the email address from the Excel file using FetchXML but I since you cannot access the dynamic values in the filter, that did not work. I know, sounds weird, but unless there is a trigger or an action before the list record step, their Dataverse columns are not available. I was using a PowerApps Trigger.
• Since I could not find the Rows using the Get Row by ID, I had a look at what other options were available, and to my surprise, there is a new action called Search rows (preview). Fantastic, just when I needed it.
• Final challenge I faced was, trying to use a condition to determine if any records were found from the search. When a record is found, the output is an Array of Objects otherwise an empty array. So, you would think that I could just test the length of the array using the expression length(array) = 0 to determine which records are found and not, well Flow does not allow this.

So, here is how to configure the Flow to deduplicate, import and update records in one go.

To start, create a blank Instant Flow

Add the PowerApps trigger since this Flow will run on demand

Add a PowerApps trigger

Next, add the List rows present in table action, and set the location of the file as per your file configuration. (Your data must be in a table format).

This action will retrieve the records from the Excel Online file

Add an Apply to each control and set the first parameter as the output value from the list rows action

The Apply to each control is a loop that will process each of the records in the Excel file

Next, add the Search rows action and set the search item to the dynamic value of the email address in the excel file, then set the table filter item to the logical name for the contact table (contact).

The Search rows action uses the email address from the Excel file to find a matching record in the Dataverse.

Next, you should add a condition control, this will be to test the result of the search to determine if a record was found with the matching email address.

The condition will test if a record is found. If no record is found an empty array is returned. empty(outputs(‘Search_rows_(preview)’)?[‘body/value’])

Next, on the if yes branch, add the Dataverse Add a new row action and map the fields from the Excel file to the Dataverse columns.

Map the Excel data columns to the Dataverse columns.

On the If no branch, add an apply to each action and set the first parameter to the output of the List rows.

If the record is found, you will retrieve the record and then update it with the values from the Excel file.

Finally, add a Dataverse Update a row action and map the columns you would like to update

Map the Excel columns to the Dataverse columns you would like to update. To clear out any values, use the null expression

There you have it, the Flow will retrieve the contacts from the Excel Online file, search for them in the Dataverse using the email address, if the record is not found, it will be created, otherwise, the record is updated as needed.

Once you have complete configuring the Flow, it should look like this.

I hope you found this article helpful.

Power Apps Interview Questions and Answers 2024

 Microsoft Power Apps offers a modern approach to developing business applications for mobile, tablet, and browser platforms. Learning  Microsoft Power Apps will help you through the creation of robust and productive apps that will add value to your organization.

This blog contains the most recent and frequently asked Microsoft Power Apps Interview Questions and Answers 2024. It serves as a valuable resource for everyone who faces Power Apps interviews.

But, before we get into the interview questions, here are some facts that show how the demand for Power Apps is growing:

• Microsoft Power Apps has been named a leader for Enterprise Low-Code Application Platforms by Gartner and Forrester Wave.
• According to Forrester Consulting, using Power Apps reduces the average cost of creating an app by 74%.
• According to ZipRecruiter, the average Power Apps developer pay in the United States is $113k per year.

Power Apps Interview Questions and Answers For Freshers

If you are just getting started in this field, these PowerApps interview questions for freshers will be helpful in your preparation

1. What are Power Apps?

Ans: Power Apps is a Platform as a Service. It enables you to create mobile apps that run on different operating systems, such as Windows, iOS, and Android. At its core, it is a suite of data platforms, connectors, services, and apps that offer a quick application development environment to create custom apps for the business requirements.

2. What programming language is Power Apps?

Ans: Microsoft Power Fx is currently powering the Microsoft Power Apps. Microsoft Power Fx is a low-level programming language used to represent logic across the Microsoft Power Platform.

3. What are the main components of Power Apps?

Ans: The main components of Power Apps that one needs to understand before developing an app are

1. GALLERY: A gallery in the app is a way to visualize data. It is a set of displays used to view and navigate data.
2. SCREEN: A screen is a method of visualizing a specific data collection or record (mobile, iPad, Desktop). There usually is one for seeing all records, one for viewing a single record, and one for editing.
3. CARD: A card is a screen area that displays a specific record from your SharePoint list or any other database from which you built an app.
4. CONTROL: Controls allow you to see and interact with your records.
5. PROPERTY: Each control has its own set of properties.
6. FUNCTION: Functions are used to interact with and change the characteristics.

If you would like to become a PowerApps Certified professional, then Enroll in Our  PowerApps Certification Training Course. This course will help you to achieve excellence in this domain.

4. List PowerApps features?

Ans: Microsoft Power Apps includes a lot of features to develop apps like a pro and, more significantly, with minimal effort. Some of them are:

• Common data service for data storage
• To integrate data and systems, more than 200+ connectors are available
• Drag-and-drop designer
• Pre-built templates
• AI builder
• Role-based user-experience customization.

[ Learn Complete Power Apps Tutorial ]

5. What are the benefits of using Power Apps?

Ans: The primary advantages of utilizing Power Apps:

• Power Apps allows you to create apps more quickly.
  
• Users can create new apps using the Power Apps design interface without knowing how to code.
  
• You can connect to many data sources and online services with simply a few programming needs.
  
• Business process automation
  
• PowerApps now include AI capabilities.
  
• Simple deployment and cross-platform accessibility
  
• It’s integrated with Microsoft Flow. 
  
• Cost-effective.
  

Are you planning to build a career in PowerApps? Sign up for this PowerApps Certification Training in Hyderabad to begin your journey today!

6. What is the difference between a Model-driven app and a Canvas app?

Model-Driven App	Canvas App
Model-Driven Apps is an AppModule that allows users to create component-focused apps to implement an end-to-end business process.	Canvas App is an AppModule that allows users to create task-specific apps with design flexibility.
To deploy and develop Model-Driven Apps, one must have:  Dynamics 365 Product Licensing ( CE : Finance: Operations)	To develop Canvas Apps, one must have: The Power Platform license Necessary Roles and Permission in Data Verse
Model-Driven Apps can connect to and interact with only one data connection, Data Verse.	Canvas Apps may connect and interact with over 350+ Connectors, allowing users to have extra features.
Can implement logic in various ways, including Business Rules Workflows Actions  Plugins	Logic implementation is app-specific and can be accomplished using Excel-style formulas and conditional checks.
Version control is not supported; once modifications are made, they must be rolled back by a subsequent deployment.	Version control is supported, and modifications can be undone with a single click.

7. Define common data services, and why should we use them?

Ans: Common data service lets the data get integrated from different sources into one store that can be easily used in Power Automate, Power Virtual Agent, Power BI, and Power Apps. This makes the app developing experience seamless.

In the Common Data Service, data is kept as a collection of entities. An entity is a collection of records used to store data like a table in a database.

Common Data Service includes a predefined collection of entities that cover common scenarios. Still, you can also create new entities that are unique to your organization and populate them with data using Power Query. App developers can then use this data to construct rich applications with Power Apps.

8. What type of apps can be created in Power Apps?

Ans: Power Apps is one productivity development platform that allows us to create canvas apps, common data services, model-driven apps, and portals.

9. Is it possible to use multiple data sources in one canvas app?

Ans: Yes. With Power Apps, you can create any number of connections. Once it is done, any number of data sources can be used in one app.

10. What are the different ways to submit data from Power Apps?

Ans: This task can be executed by using two functions – Patch() and Submit form(). However, Patch() can also be used to upload partial data.

11. Can we access local network data sources in Power Apps?

Ans: Yes, we can easily connect to the local network data sources.

12. How can Error Handling be implemented in Power Apps?

Ans: To do so, first, we will have to get information about any errors through the Errors function. And then, through Validation and DataSourceInfo, some of the errors can be ignored even before they take place.

The Canvas App introduced the 'IfError' and 'isError' functions, to manage errors and display the appropriate message.

Formula-level error management must be enabled in order to use these features. To enable it, follow the steps below:

• To begin, start the Canvas App and choose File.
  
• Under Settings, select Advanced Settings.
  
• Allow for formula-level error management.
  

13. How is it possible to use media files in the Canvas app?

Ans: Up to 200 MB of media for each app can be uploaded to Power Apps. However, what is majorly recommended is using media/blog storage services, such as Azure Media or Azure Storage, and embedding the media URL to the app.

14. What is a Power Apps collection?

Ans: A Collection is a list of elements or an array. A Power Apps Collection is an array that aids in the storage of data in Power Apps memory. You can later use the saved data in a variety of ways. You can also save the data in a PowerApps Collection to any data source, such as a SharePoint Online List, an Excel document, or a database. To operate collections in Power Apps, Collect, Clear, and ClearCollect functions can be used.

15. How do you create different user environments in Power Apps?

Ans: An environment is referred to as the space to keep, manage, and share the business's data, apps, and flow. It can also be regarded as a container that distinguishes apps based on different target audiences, security needs, and roles. However, creating or choosing the environment used depends on the company and the apps you are thinking of developing.

16. How many types of variables are there in Power Apps?

Ans: Local, global, and collections are the three types of variables.

1. A local variable is a single-row variable that lives exclusively on a single screen. As a result, you won't be able to use this variable across your Power Apps.
   
2. A global variable is a single row variable that exists throughout all of your Power Apps and may be used on any screen.
   
3. Collections are variables containing numerous rows of values. They can be viewed as arrays or tables. You may use collections in all of your Power Apps.
   

17. How can a local or global variable be defined or used in Power Apps?

Ans: To do so, the Set function is used to set the global variable’s value. This holds an information piece temporarily, like the result of operational data or the number of times somebody has clicked a button. And then, the UpdateContext function is used to create the content variable, which holds information temporarily.

The syntax for global variables:

Set( VariableName, Value );

Example:

Set( Prasad, 10 );

The syntax for local variable:

UpdateContext( { ContextVariable1: Value1 [, ContextVariable2: Value2 [, ... ] ] } );

Example: 

UpdateContext( { Name: "Radha", Score: 8} );

18. Is it possible to create Power Apps without gaining access to a license?

Ans: No. Neither creation nor consumption of Power Apps is possible without a license.

19. What is a flow in Power Apps?

Ans: Flow in Power Apps allows you to respond to an event in one service (like SharePoint online) and then do something with the data in another service (such as Twitter).

20. What is the difference between Power Apps and logic apps?

Ans: Azure Logic Apps is a service that allows you to integrate apps, build workflows, and more. Power Apps are used to create graphical user interfaces. Since almost everything can be referred to as an application or an app, it's not logical to suppose that all applications are similar in some manner.

21. What are the different types of data sources that can link to data window controls?

• Quick Select: Used when records come from one or more tables linked by a foreign key.
  
• SQL Select: In addition to the preceding grouping and computed columns, SQL Select allows for the designation of additional columns.
  
• External: The records can be imported from a flat document or populated from code. (For example, the employment of set item() tactics)
  
• Query: Utilised when the data supply (SQL declaration) is saved in a query item defined in the query painter.

22. How can you boost the performance of Power Apps?

• Limit the number of data connections.
  
• Reduce the number of controls.
  
• Optimize the OnStart property.
  
• Should cache LookUp data.
  
• Avoid control reliance between screens.
  
• Use delegation.
  
• Use Delayed Load.
  
• Working with huge datasets.
  

PowerApps Interview Questions For Experienced

Moving on, if you are an experienced Power Apps professional who has been in the industry for a while, these Power Apps interview questions will help you ace your next job interview.

23. Discuss the concept of Power Apps security roles?

Ans: In Power Apps, security roles can be used to restrict access to certain apps and data in the environment, or they can be used to restrict access to all resources in the environment.

Security roles control a user's access to an environment's resources by assigning them access levels and permissions. The access levels and permissions specified in a certain security role set the limits on the user's view of apps and data and the user's interactions with that data.

24. What are DLP policies in Power Apps?

Ans: To safeguard data in your organization, you may use Power Apps to define and enforce policies that restrict which consumer connectors can share with certain business data. These policies are known as data loss prevention (DLP) policies.

DLP policies ensure that data is controlled uniformly across your organization and prevent essential company data from being accidentally shared to connections such as social networking sites.DLP policies can be defined and managed at the tenant or environment level using the Power Platform admin center.

25. What is the difference between the Combo box and dropdown in Power Apps?

Ans: Combo boxes and dropdowns are two Power Apps controls that are frequently used to offer a list of options to users.

• Combo box: A control that lets users choose from options. Search and multiple selections are supported.
  
• Dropdown: A list displays only the first item unless the user opens it.

26. Describe SaveData, LoadData, and ClearData functions in Power Apps?

SaveData function: It saves a collection under a given name for later use.

Syntax: 

SaveData( Collection, Name )

LoadData function: It reloads a previously saved collection by name. This function cannot be used to load a collection from a different source.

Syntax:

LoadData( Collection, Name [, IgnoreNonexistentFile ])

ClearData function: It clears all storage associated with the program if no name is specified.

Syntax:

ClearData( [Name] )

27. What is the difference between IsMatch, Match, and MatchAll functions in Power Apps?

Ans: The IsMatch function compares a text string to a pattern, which can be a random string, a regular expression, or a predefined pattern. The MatchAll and Match functions return the match's contents, including any sub-matches.

28. What’s the difference between Lookup and Filter Functions?

The LookUp function finds the first element in a table that matches a given formula. LookUp can be used to locate a single record that meets one or more criteria.

Syntax:

LookUp(Table*, Formula [, ReductionFormula])

The Filter function is used to identify records in a table that meet formula criteria.

Syntax:

Filter(Table*, Formula1 [, Formula2, ... ] )

29. What is Power Apps delegation?

Ans: Delegation is the point at which the expressiveness of Power Apps formulas reaches the need to limit network data transfer. In short, rather than transmitting data to the app for local processing, Power Apps will outsource data processing to the data source.

Working with huge data sets necessitates the use of delegated data sources and formulae. It's the only way to keep your app running smoothly and guarantee users have access to all of the information they require.

30. What is the use of the Power Apps Loading Spinner?

Ans: In PowerApps, a Loading Spinner is an animated element that displays that loading is in progress. The loading spinner appears when the data is slowly loaded. This means that it aids in informing the user that the process is in busy mode and that it may take some time for something to materialize.

It is also known as the SpinnerLoaderProgress indicator. Similarly, in PowerApps, if data loading is slow in the app and you want to place a spinner to reduce the wait time, this is referred to as a PowerApps Loading Spinner. Microsoft included a "LoadingSpinner" option in the Advanced tab as well as the Drop-Down menu in the upper left corner of the app.

31. Explain the Distinct function of Power Apps?

Ans: The Distinct function applies a formula to each table entry and returns the results in a one-column table, deleting duplicate values. The column's name is the result.

The formula has access to the fields of the current record being processed.

Syntax:

Distinct( Table, Formula )

Table: The table is used to evaluate across.

Formula: A formula is used to evaluate each record.

32. What are Power Apps templates?

Ans: Templates are a quick and straightforward method to create apps that link to various data sources. Log in to PowerApps.com to access the pre-built templates. However, there are some advantages to using templates:

• You can use or clone pre-built layouts and color palettes for different app screens.
• Learn how to configure controls for common actions such as submitting data from a form by clicking a button, switching from one app page to the next, presenting a list of items from my data, and so on.
• Learn how data enters and exits your app, as well as how to connect it to your data source.
• Learn how to integrate a GPS into your app.

33. What are the differences between Power Apps workflows and Power Apps pages?

Ans: All users can see the page. A workflow is solely visible to the user who developed it unless it has been shared with others. A Power App page can be viewed inside the Power Apps, however, a Power App workflow action cannot be viewed inside the Power Apps.

34. What exactly is the difference between Power Apps and Power Automate?

Ans: Power Apps is a declarative environment that uses connectors to enable content access through a customizable user experience. Power Automate is a workflow engine that uses the same connectors as Power BI to provide automatic content access.

35. What exactly is delay output in Power Apps?

Ans: When DelayOutput is set to true, there is a second delay before the changes are recognized. This is done to give you time to complete typing what you want. When used to aid TextInput, the delay works well.

36. What would you do if a Gpf was found in your application?

• Examine the library search direction for improvement, required DLLs, runtime PBLs, and many other things.
• If PFC is used, make sure the PB version is the same as the PFC version, i.e. 5.0.04, and so on.
• Use a DLL tracking application to test the executables' DLL list.
• Enabling PB Debug Tracing statistics and finding the last line of code performed prior to the GPF.

37. Is it possible to use the canvas app in a model-driven app?

Ans: Canvas apps can be added to model-driven forms in the same way that other custom controls can. Rich data integration capabilities in an embedded canvas app integrate contextual data from the host model-driven form into the embedded canvas app.

38. When there are frequent updates, how do you ensure the versioning of a canvas app in a collaborative environment?

Ans: While saving the app, you can leave a version-specific remark or comment. You may also place a label on the app's Home screen to represent a version number that the app's creator can manually update.

39. What are two different types of Power Apps authentication?

• OAuth-based authentication: It authenticates the app requesting access to the Power Apps service, removing the need for users to remember their Power App password or Office 365 account credentials. Power Apps handles all authentication and authorization.
• Password-based authentication: Power Apps supports both user name and password-based authentication, and the Power Apps developer can choose which kind to employ for Power Apps forms. This can be configured using Power Apps attributes (Form > Security Type).

40. What are Power Apps' limitations?

Ans: Power Apps have limits that companies should be aware of despite their strength and breadth:

• Licensing is restricted under Microsoft 365.
• A complicated licensing structure
• Services that use a low-code
• Power Apps' integrated development environment
• There is limited support for a number of device sizes and screen orientations.
• The connector ecosystem's throughput limits
• There is no shared code or functions.

Conclusion

Wrapping up the article, by now, you would have understood how effective Power Apps is. If you're getting ready for an interview, these Power Apps interview questions will prove to be helpful.