Best way to compare Dataset data with SP List data and update values if different

[dhhenrique]

Hello,

I'm building a Power Apps that uses a SP List as database (because it's a free connector).

Initially, the data should come from an SQL database to feed the SP List.

I'm planning on doing this like below:

1 - Using Power BI, I create a DATASET that does the ETL from the SQL and schedule it to refresh daily.

2 - Then with power automate I get the clean data from the DATASET and update it in the SP List.

3 - The Power App connects to the SP List and users can view data and also modify some fields of the records.

I was wondering whats the best way to accomplish the second step and breaking it into more details, I want to:

     2.1 Get updated data from DATASET.

     2.2 Get items from SP List.

     2.3 Compare each record from DATASET and SP List.

     2.4 Update records that has been changed in the DATASET.

     2.5 If record doesn't exist, create record.

The critical part it's 2.2, because loops in power automate are very slow. So I was thinking about doing it with office scripts in excel, that can handle loops much better.

Does anyone think of a better way of doing this comparison or also a better way to archtecture this "pipeline".

Thanks!

[lbendlin]

    2.1 Get updated data from DATASET.
     2.2 Get items from SP List.
     2.3 Compare each record from DATASET and SP List.
     2.4 Update records that has been changed in the DATASET.
     2.5 If record doesn't exist, create record.

Way too much effort.  Delete all items from the sharepoint list and then refill the list from the SQL server table rows. Set parallelism to max.

[dhhenrique]

Hi, thaks for the reply. I can't really do that because I would lose ID reference, and because loops in Power Automate are too slow it would take to long, I'm trying to avoid that.

[lbendlin]

What ID reference?  Are you syncing the list and the table both ways?  How would that be any faster than a max parallelism refill?

[dhhenrique]

There are other lists that reference the records in this one by the ID.

Maybe I missing something, but to refill the SP List I think I would have to DELETE and CREATE item by item in a loop, and this takes several minutes (to fill it up first time was 14min about 1000 itens).

Usually with Office scripts it would only take a few seconds to compare all the itens and return an array with the ones that has differences (I estimate less than 50 each time), then to update it, it would take about 1 or 2 minutes.

So I'm imagining the time diference to be 30 minutes against 3 or 4 minutes. It might be even more if i have much more than 1000 itens.

How do you see it?

[lbendlin]

Ah, you didn't mention the interaction with other lists before.

I would be interested in your experience using Office scripts for this.  At the end the day everybody seems to gravitate towards the SharePoint APIs for maximum performance and flexibility but who knows, maybe Office scripts use a secret and/or better connector?

[dhhenrique · Accepted solution]

Hi,

I ended up doing the way I first thought, here is the result:

In this run, the SP List had already 580 itens, and the SQL table had 975. 

The 580 itens in the list still had the same values in the SQL, so it only had to return the 395 new itens.

As you can see, the Office Script do all the comparisons very quick (11s). I observed that the time it takes doesn't change much as I increased the number of comparisons, basically the few seconds (5~15) it's the time to call the connector and return the results. 

Here are the prints of the flow run:

ps: When I did this run, the 'itens compared counter' was in the wrong place, it's 975 not 580.

Here it is the main loop in the Office Scripts code:

[takolota]

@dhhenrique 

You can also update & create around 1000 SP in under a minute from SQL using batch actions.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoin...

And if that seems like too much to set up, you can still directly compare what rows are in SQL not in SP by using a Select action to create an array of just your key values in SP, then use a Filter array on the SQL values set to check if each SQL item key is contained in the SP key array. That does the comparison & returns the records needing creation in seconds or less, no Power BI workarounds required.