Unanswered

Lookup one value in list against another

(0) Share

Report

Posted on by Kmayes

792

look at Hi Community

I have a situation where I'm trying to create a flow that checks through one list of items against another list.

The first list of data is collected from running a query against a dataset (Power BI) and the second list is a SP list. I want it then to add the item from the Power BI Dataset to the SP list if it doesn’t already exist. So really need to look at the items in the Power Bi data and check it against those in the SP list.

What is the best way of creating a flow that meets this criteria.

Categories:

Using flows

I have the same question (0)

All responses (12)

Answers (0)

DJ_Jamba 2,837 Super User 2025 Season 2 on at

Like (0)

Report

What is the common id, key or foreign key on both sets of data?

Was this reply helpful? Yes No
Kmayes 792 on at

Like (0)

Report

@DJ_Jamba There is a Ticket ID on both sides

Was this reply helpful? Yes No
Suggested answer

DJ_Jamba 2,837 Super User 2025 Season 2 on at

Like (0)

Report
OK, so you already have the data from Power BI in one action (Parse JSON) and the data from SharePoint in another action (Get items).

1. After the Get Items action, add a Select action, switch to Map text input mode and configure as follows:

From: @{outputs('Get_items')?['body/value']} Map: @item()?['Ticket ID']

2. Add a Filter array action and configure as follows:

From: @body('Parse_JSON') Filter (edit in advanced mode): @not(contains(body('Select_SharePoint_Ticket_Id_only'), item()?['Ticket ID']))

This will result in all Power BI rows where the Ticket ID does not exist in your SharePoint list

Was this reply helpful? Yes No
Kmayes 792 on at

Like (0)

Report

@DJ_Jamba Im not sure if im being daft or done something wrong but there seems to be alot of items which makes me think it has collected all items that are on the SP list as I would only expect a few items

Was this reply helpful? Yes No
w.p 8,339 Super User 2025 Season 2 on at

Like (0)

Report

@Kmayes

If you follow DJ_Jamba's instructions, it should work.

Was this reply helpful? Yes No
Kmayes 792 on at

Like (0)

Report

@w.p @DJ_Jamba

I have followed the instructions @DJ_Jamba kindly provided however the amount of items it has returned is the tota amount in the PBI data. After the data I added in an append to array to see what items its picking up in the filter element and its 501 items which is the grand total of items. Either its not filtering correctly or I have put an element incorrect so I have attached a copy below to show

Was this reply helpful? Yes No
w.p 8,339 Super User 2025 Season 2 on at

Like (0)

Report

@Kmayes

are able you to show sample data from the select output and the ticket ID from the parsed JSON?

Was this reply helpful? Yes No
Kmayes 792 on at

Like (0)

Report

@w.p

Sure! This is from the select output

And this is a sample from the parsed JSON

Was this reply helpful? Yes No
Suggested answer

w.p 8,339 Super User 2025 Season 2 on at

Like (0)

Report
@not(contains(body('Select_SharePoint_Ticket_Id_only'), item()?['MIGRATED REVENUE C4C[TICKET_ID]']))

Was this reply helpful? Yes No
Kmayes 792 on at

Like (0)

Report

@w.p

When I put this code I now get this error message

Was this reply helpful? Yes No