I have a model-driven app using Dataverse and am trying to count all the unique records in a column, but don't seem to be able to.
I'm looking or something similar to 'select count(Distinct(columnName)) from tableName' like in SQL.
Tried creating a Canvas Page with the distinct function, but it definitely does not count all records - probably some delegation issue without actually displaying any error.
Hi @Aevoth ,
You can get unique records by using a fetchXml query. Please refer to my two blogs:
Good Luck !
Thanks @melamri for getting back to me. I tried your suggestions, unfortunately I can't use the 'HTTP with Azure AD connector' due to DLP policies that block it. A custom connector is again out of the question for the exact same reason. Tried the Premium HTTP connector, but then again, can't authenticate...
I'm a bit stumped as I really did not expect such a common scenario to take so much effort, and it's not even resolved!
Your suggestion even gave me the idea to try and use an OData filter to get distinct values, but again, the 'List Rows' connector had no idea what to make of it (no wonder, $apply is not in the list of supported query options for Dynamics 365: docs.microsoft.com/en-us/dynamics365/fin-ops-core/..)
Any other ideas?
I understand that you can't use the HTTP with Azure AD connector. For this reason, I had shared two approaches.
The second one uses the dataverse connector, but you have to implement a custom api on dataverse to execute your fetchXml. Have you been able to check this option?
I refer to the following blog: https://xrmtricks.com/2022/03/16/power-automate-dataverse-how-to-execute-a-fetchxml-query-that-inclu...
Please tick Accept as solution if the answer is useful.
Thanks @melamri. I did read your article, which does look elegant, but I'm afraid you lost me at creating a custom api.. I looked at Tutorial: Write and register a plug-in (Microsoft Dataverse) - Power Apps | Microsoft Docs, but unfortunately, I can't download any apps onto my device and can only access the Power Platform from the work device to register it. Is there a way I can build the plugin on my personal device and register the plugin somehow without the registration tool? Or PowerShell, or any admin access to Azure, none of which I have unfortunately... Thanks
Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.
Your chance to join an engaging competition of Power Platform enthusiasts.