Update hosted table from Excel file using Power Automate

[bbaker_tngeo]

I'm not sure if this is possible, but I am trying to create a flow that would perform all steps of a workflow instead of just the initial step. Here's my scenario:

-Automated email gets sent from a 3rd party software with a summary of assets

-Power Automate gets the attachment, uploads it to Sharepoint, and then cleans up the headers and other unnecessary rows using an Office Script

-List rows in the Excel file

-Get data from feature layer

This is where I get stuck. The end goal is to compare the Excel table to the hosted table and get 2 arrays - one that has records in the Excel file that are not in the hosted table (adds) and records in the hosted table that are no longer in the Excel file (deletes). The records that persist do not need to be updated, as there are additional fields in the table that are updated in Portal once a record is added to the table.

Ultimately, I want to be able to use the Add records to a feature layer and Delete a record from a feature layer actions with the arrays described above, but am not sure how to get to this point. I have tried using a series of Select actions followed by a Filter array, but it doesn't return what's different. I am thinking it has to do with how the data is being returned as an object instead of a list of strings.

I have a python script that will do exactly what I need it through via pandas, but I would like to keep it all within Power Automate, if possible, so I don't have to maintain 2 separate components. Does anyone have any ideas that might get me on the right track?

[AkshayHarshe]

I am assuming that you have some common field names in both your feature layer and excel collection. If you don't then you might need few additional steps. One of your array comes from the excel and the other from the get data from feature layer action.

For creating and comparing arrays I found this blog which might help: Quickly Comparing Two Arrays in Power Automate and Logic Apps – William's IT Blog 🍉

You can also consider to start a flow by triggers like when a file is modified in Share point or OneDrive connectors

[bbaker_tngeo]

Hi Akshay, thank you for the response. Yes, there is a unique ID in each dataset that I am using to compare.

I have essential done that, but when I filter the array, it comes back with all records, which means it's not filtering correctly. I read somewhere that it is because the Select actions are returning objects and not a list of IDs as texts, but am not sure if that's the case, or if it is, how to get a different output.

Here's a test of the run, with Compose actions to confirm the lengths of the arrays being output. I added an additional Compose action to union the ArcGIS Array with itself to make sure there were no duplicate values that might be causing an issue:

Here is an example of the output of the Select action(s):

Do you have any ideas that might cause the Filter array to work as-expected?

[abureaux · Accepted solution]

Swap your Select from "key value mode" to "text mode":

This will ensure you just get the IDs

[bbaker_tngeo]

That was exactly the issue. I was able to complete the workflow after modifying that action per your feedback. Thank you!