Unanswered

Count Unique records in a column with Model-driven app

(1) Share

Report

Posted on by Community Power Pla...

Hey all,

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.

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

meelamri 468 on at

Like (0)

Report

Hi @Anonymous ,

You can get unique records by using a fetchXml query. Please refer to my two blogs:
- https://xrmtricks.com/2022/03/12/canvas-apps-dataverse-how-to-get-distinct-values-from-datasource/
- https://xrmtricks.com/2022/03/16/power-automate-dataverse-how-to-execute-a-fetchxml-query-that-includes-options-not-supported-by-the-list-rows-action/

Good Luck !

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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/..)
$apply=groupby((ColumnName))

Any other ideas?

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (0)

Report

Hi @Anonymous ,

Maybe power automate can do this.

Best Regards,

Wearsky

Was this reply helpful? Yes No
meelamri 468 on at

Like (0)

Report

Hi @Anonymous

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-includes-options-not-supported-by-the-list-rows-action/

Please tick Accept as solution if the answer is useful.

https://xrmtricks.com/

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No