Dataverse Delegations

[Anonymous]

Hi,

I'm currently doing some RnD on dataverse for a project.

The app I'm working on filters any excel file uploaded by the user and exports the resulting data to their local storage, but I came across the 500 data limit, which I did extend to 2000 but I'm expecting around 10000+ rows for this filter & export app, so checking out if dataverse is viable for this particular case.

If there's no workaround for the 2000 row limit then is it possible to delegate the filtering job to dataverse to apply the filter to all existing records and then export it from there to user's local storage or one drive. and also any licensing limitations for dataverse 
Alternatively can this filtering and exporting job be done by power automate too? If so, which would be better? Please show me some resources which I can refer for this.

I'm really new to the power platform so please bear with me, thank you in advance

[v-xiaochen-msft]

Hi @Anonymous ,

Power automate can meet your requirement.

Please see here

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[PaulD1]

So, the goal is to take a local Excel file, perform a filter on it and to save the filtered results to a local file (Excel, CSV?)? And this should be automated (user just selects the Excel file and possibly a name/location for the output file)?

If there is no need for the data to be stored in the cloud and the users will be on Windows PCs, venerable Access would be well suited to this task (and only the developer needs a license, the users can run an Access App under the freely available Access Runtime). It can read from Excel files, has pretty good querying/filtering capabilities, can output Excel or CSV to local storage and you won't hit any delegation limits.

Canvas Power Apps are primarily mobile focused and limited to accepting a maximum of 2000 rows from any data request whether you use Dataverse, SharePoint or SQL as your data source, though you may be able to build a solution to 'page' through results (e.g. make five requests for 2000 rows each and then combine them to get 10000 rows).

SQL and Dataverse are premium offerings, so every user needs a license (min $5 per user per month).

There is a cut-down/limited version of Dataverse that can be accessed only from Power Apps within Teams that does not require premium licenses. I don't know much about it as it is not a good fit for the sorts of solutions I work on so have never used it.

SharePoint does not require a license either but delegation support is poor and it is very slow for writing/updating large numbers of records (if you need to write thousands of rows you will likely be looking a minutes rather than seconds).

[Anonymous]

Hi, @PaulD1, thanks for your reply.

I've never used access before so I got a few questions, This will be coming from me since I'm currently working on the power platform, Is it possible to get Filtering parameters from the user through a power apps UI pass them on to access apply the filter on the excel file and download the new filtered file in the user's local storage or one drive? Currently I'm trying out power automate as a solution as mentioned by @v-xiaochen-msft  but there seems to be some limitations in the filtering queries I can apply, which I'm still looking into, Is access more accepting of these filtering queries?

[Anonymous]

Hi, @v-xiaochen-msft, I really appreciate it, I'm currently trying out your suggestion but I found some filtering query limitations in power automate, specifically the issue of filtering field accepting only one column as input

[v-xiaochen-msft]

Hi @Anonymous ,

There is a solution to help you filter items on multiple columns.

For example:

You could filter on a column first by using odata filter.

Then you could use filter array action:

At this point you could continue to filter the results of the previous step.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[Anonymous]

Hello, @v-xiaochen-msft,

Can you give me a documentation reference for odata filter query? I can't find any proper source

[v-xiaochen-msft]

Hi @Anonymous ,

I modified my previous reply.

Please check it .Thanks.

Best Regards,

Wearsky

[Anonymous]

Hi, @v-xiaochen-msft, 

That is helpful but I'm stuck in a step before that, I'm using the substringof() function but the test throws an error, 
An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

I tried looking through the documentation but could you help me with this?

[v-xiaochen-msft · Accepted solution]

Hi @Anonymous ,

Power automate doesn't has the 'substringof' function.

It has substring function .

Please see here.

Maybe you are asking about odata query?

You could use contains instead of substringof.

Please see here.

Best Regards,

Wearsky