Power Apps Canvas Apps offers the Distinct function that returns unique values from a table or data source. Unfortunately, this function may not work as expected if the data source contains a large data set. Let’s look at this with an example by reproducing the issue:

• Let’s take the Dataverse table ‘Contact‘ and its column ‘Address 1: Country/Region‘
• The idea is to display all countries from all contacts in a Canvas App using a drop-down control.
• The total number of rows in this table is equal to 1000

In order to have all the countries in the contact table, we can use the following formula:

Distinct(Contacts,'Address 1: Country/Region')

To find out the actual number of countries, the following formula is used:

CountRows(Distinct(Contacts,'Address 1: Country/Region'))

As the data source used is Dataverse then it will be possible to check if the result is correct by using a fetchXml query:

<fetch distinct="true" >
  <entity name="contact" >
    <attribute name="address1_country" />
  </entity>
</fetch>

The retrieved result is 122 and is different from the one retrieved on Power Apps.

So why do we have this gap in results? In fact, the Distinct function is executed after retrieving the contacts from the server. As the Contact table has 1000 records and the app is limited to retrieve 500 records at most. So the Distinct function can only return unique values from a part of the data and not from the whole table.

After changing this parameter to the maximum value of 2000. Then the Distinct function displays the 122 unique countries that are on the Contact table.

That is correct since the total number of contacts is less than 2000. Otherwise, the Distinct function could return a wrong result.

---

To get the right result, it would be necessary to go through a fetchXml query. Unfortunately, it is currently not possible to execute this type of request from a Canvas App. At this level, a cloud flow might be a good option.

Indeed, the Dataverse connector allows to execute fetchXml queries.

After a few tries, the cloud flow runs with an error in the action List rows:

After checking the documentation, it turns out that the distinct option is not supported in fetchXml queries from the List rows connector 😦

Let’s try another approach. Indeed, fetchXml queries can be performed through an http request. Always with the same scenario. The http request which corresponds to the execution of the fetchXml query is:

https://myOrg.crm12.dynamics.com/api/data/v9.0/contacts?fetchXml=<fetch distinct="true" > <entity name="contact" > <attribute name="address1_country" /> </entity> </fetch>

To execute this http request. We can use the HTTP with Azure AD connector:

The flow returns to the Power Apps that ‘value’ array using the expression: body(‘Invoke_an_HTTP_request’)[‘value’]

The flow can be executed when a screen is displayed. Then get the response of the flow and transform it into a collection.

• Flow name: getDistinctAccountCountries
• Flow output: result
• collection name: countriesCollection

UpdateContext({countries: getDistinctAccountCountries.Run().result});
ClearCollect(
    countriesCollection,
    MatchAll(
        countries,
        "\{""address1_country"":""(?<address1_country>[^""]*)"",""address1_composite"":""(?<address1_composite>[^""]*)""\}"
    )
);

By passing the countriesCollection to the drop down control. It displays all the contact’s countries.

---

To conclude, the use of the HTTP with Azure AD connector remains a fast way to execute fetchXML queries that is not supported by the Dataverse connector. However, I still don’t know how this connector will react after a deployment to another environment via a solution. Have you ever experienced this? Please feel free to share your own experience in the comments section.

In my case, I plan to implement a CustomApi that will take in input a fetchXml that it will execute and return its result. Then call this CustomApi using the Dataverse connector “Perform an unbound Action”, I will try to share this soon.

Hope it helps …