Updating dataverse table with extra columns from dataflow

[vffdd]

I have a dataflow that picks up a CSV file and uploads to a dataverse table.  This is refreshed periodically.   I have an additional text column in the dataverse table (it's not in the dataflow) that is written to from a powerapp.  (I need this field to filter a gallery in powerapps)

When I refresh the dataflow I would like the following to happen.

Existing items in the table that have blank in the extra column to be updated from the flow

All new items  to be added

And any items in the table that have "Yes" in the extra column to be deleted..

What's the best way to achieve this?

I have a feeling it's going to be some kind of a round about solution ! 

TIA

[AhmedSalih]

Hello, @vffdd, to delete the records that have "Yes" in the extra column to be deleted, use a Power Automate Flow that will run after your Dataflow refresh complete using "When a dataflow refresh completes" connector.

Then you will use the List rows action with a query filter to only get those with the "Yes" value. And then use Apply to Each action taking the value output of the List rows action and use the Delete Row action inside the Apply to Each using the row id (the unique identifier) from the list rows action.

For your second requirement, "

Existing items in the table that have blank in the extra column to be updated from the flow

All new items  to be added", You can use a timer that run every minute to refresh the Dataverse table that is getting loaded from the Dataflow.

 

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[KoenJanssensPD]

I think you will have to include your dataverse table into your dataflow as well, so you can combine the records from the CSV with the records from the dataverse table, so you can either update existing or create new rows depending if there is a match, taking into account the additional column...

[vffdd]

ok I definitely prefer the idea of doing this @KoenJanssensPD rather than running a flow..because I need to run a flow anyway to update the dataflow from csv so I'd rather just have the one.. but how would I do this?

[KoenJanssensPD]

you should merge the rows from the CSV together with the rows from the dataverse table, keeping only the ones you need (the new and the updated ones), deleting the rows with "yes" in the extra column. This will require some power query tricks to merge and filter the records...

then in the dataflow option you should enable the option "Delete rows that no longer exist in the query output"...

You could also run a powerautomate flow to delete rows with "yes" in the extra column, so that they don't appear in the dataverse records in your dataflow...

[vffdd]

ok great thanks I'll give that a go !

[vffdd · Accepted solution]

Thanks for all your help both of you but  I've actually decided to solve it another way.  I've created a separate table with the extra column and link it to the main table with a uniqueId and that way I can overwrite the main table no problem and use both tables as data source for the gallery. I also use a powerautomate to delete the rows in the 2nd table when no longer existing in the main table..

[KoenJanssensPD]

In Dutch we say "all the routes lead to Rome"; so as long as you achieve what you need (and it is not too complex to maintain), it doesn't matter how you do it 😉