Compare Parse JSON to Sharepoint list with Power Automate

Question

I created a flow (working!) to copy an Excel list to a Sharepoint list. The Excel list is replaced weekly so, before Excel items are copied over to SP, the new Excel list must be compared to the existing SP list. Based on data from six columns, any Excel item that duplicates a SP item will be ignored and not copied to SP. Since I can't figure out how to compare one list to the other, I can't try the yes/no condition.

Working Flow

Comparison fields from Excel are Carrier ID, Policy Number, Policy Effective Date, Rpt. No, Corr. Seq. No., and Corr. Type.

Fields from Excel

Comparison fields from Sharepoint list are Carrier, PolicyNo, EffDate, RptNo, CorrSeqNo, and CorrType.

I've concatenated columns for both list, to make unique values but can't figure out how to compare the two and populate SP list

Concat

I've tried variables but can't figure out how to set up variables as concats.

Variables1

Variables2

I read about adding all items to SP list then deleting dups but, because of the multiple field comparisons, I can't figure out how to delete dups. There's also many solutions for a single column comparison but I haven't found anything about multiple column comparison.

I've also tried Phind.

Is there a way to compare the two lists and add to SP only new items from Excel or to add all items then delete dups?

Answer 1

If I understand you correctly you essentially want to compare two data sets to see what exists in one side compared to the other.

You can actually do this very easily using the Advanced Data Operations connector and the Join operation.

https://learn.microsoft.com/en-us/connectors/advanceddataoperatio/ https://www.statesolutions.com.au/join/

From there, you can isolate the records that you need to process/work with.

This is a basic flow that demonstrates the capability.

So to explain each step further ...

Current Data

This is an array that simulates existing data that you want to compare against. That could reside in a spreadsheet, SP list, DB or some other storage medium.

[
  {
    "Field1": "Field Value 1.1",
    "Field2": "Field Value 1.2",
    "Field3": "Field Value 1.3",
    "Field4": "Field Value 1.4",
    "Field5": "Field Value 1.5",
    "Key1": "Key Value 1.1",
    "Key2": "Key Value 1.2",
    "Key3": "Key Value 1.3"
  },
  {
    "Field1": "Field Value 2.1",
    "Field2": "Field Value 2.2",
    "Field3": "Field Value 2.3",
    "Field4": "Field Value 2.4",
    "Field5": "Field Value 2.5",
    "Key1": "Key Value 2.1",
    "Key2": "Key Value 2.2",
    "Key3": "Key Value 2.3"
  },
  {
    "Field1": "Field Value 3.1",
    "Field2": "Field Value 3.2",
    "Field3": "Field Value 3.3",
    "Field4": "Field Value 3.4",
    "Field5": "Field Value 3.5",
    "Key1": "Key Value 3.1",
    "Key2": "Key Value 3.2",
    "Key3": "Key Value 3.3"
  },
  {
    "Field1": "Field Value 4.1",
    "Field2": "Field Value 4.2",
    "Field3": "Field Value 4.3",
    "Field4": "Field Value 4.4",
    "Field5": "Field Value 4.5",
    "Key1": "Key Value 4.1",
    "Key2": "Key Value 4.2",
    "Key3": "Key Value 4.3"
  },
  {
    "Field1": "Field Value 5.1",
    "Field2": "Field Value 5.2",
    "Field3": "Field Value 5.3",
    "Field4": "Field Value 5.4",
    "Field5": "Field Value 5.5",
    "Key1": "Key Value 5.1",
    "Key2": "Key Value 5.2",
    "Key3": "Key Value 5.3"
  }
]

New Data

This is data is incoming and is used in the comparison process. It may include new records, includes existing records and also be missing records that exist in the Current Data data set, i.e. deleted records.

For this exercise, it crosses all three scenarios.

[
  {
    "Field1": "Field Value 1.1",
    "Field2": "Field Value 1.2",
    "Field3": "Field Value 1.3",
    "Field4": "Field Value 1.4",
    "Field5": "Field Value 1.5",
    "Key1": "Key Value 1.1",
    "Key2": "Key Value 1.2",
    "Key3": "Key Value 1.3"
  },
  {
    "Field1": "Field Value 4.1",
    "Field2": "Field Value 4.2",
    "Field3": "Field Value 4.3",
    "Field4": "Field Value 4.4",
    "Field5": "Field Value 4.5",
    "Key1": "Key Value 4.1",
    "Key2": "Key Value 4.2",
    "Key3": "Key Value 4.3"
  },
  {
    "Field1": "Field Value 5.1",
    "Field2": "Field Value 5.2",
    "Field3": "Field Value 5.3",
    "Field4": "Field Value 5.4",
    "Field5": "Field Value 5.5",
    "Key1": "Key Value 5.1",
    "Key2": "Key Value 5.2",
    "Key3": "Key Value 5.3"
  },
  {
    "Field1": "Field Value 6.1",
    "Field2": "Field Value 6.2",
    "Field3": "Field Value 6.3",
    "Field4": "Field Value 6.4",
    "Field5": "Field Value 6.5",
    "Key1": "Key Value 6.1",
    "Key2": "Key Value 6.2",
    "Key3": "Key Value 6.3"
  }
]

Join (Current Data NOT IN New Data)

As the description reads, it returns all of the items from the Current Data data set that DO NOT exist in the New Data data set.

Result

[
  {
    "Field1": "Field Value 2.1",
    "Field2": "Field Value 2.2",
    "Field3": "Field Value 2.3",
    "Field4": "Field Value 2.4",
    "Field5": "Field Value 2.5",
    "Key1": "Key Value 2.1",
    "Key2": "Key Value 2.2",
    "Key3": "Key Value 2.3"
  },
  {
    "Field1": "Field Value 3.1",
    "Field2": "Field Value 3.2",
    "Field3": "Field Value 3.3",
    "Field4": "Field Value 3.4",
    "Field5": "Field Value 3.5",
    "Key1": "Key Value 3.1",
    "Key2": "Key Value 3.2",
    "Key3": "Key Value 3.3"
  }
]

Join (New Data NOT IN Current Data)

This does the reverse to the previous step. It returns all of the items from the New Data data set that DO NOT exist in the Current Data data set.

Result

[
  {
    "Field1": "Field Value 6.1",
    "Field2": "Field Value 6.2",
    "Field3": "Field Value 6.3",
    "Field4": "Field Value 6.4",
    "Field5": "Field Value 6.5",
    "Key1": "Key Value 6.1",
    "Key2": "Key Value 6.2",
    "Key3": "Key Value 6.3"
  }
]