Unanswered

Filter Array

(0) Share

Report

Posted on by meskerkj

Hello, I have a flow that pulls data from Power BI and put it into an array, then pulls data from Excel and puts it into an array. Both arrays contain a unique identifier and the identifier has the same name in both arrays. I now need to filter 3 ways:

1.) Find all items in the Power BI array that are not in the Excel array. These will be added to the Excel spreadsheet later.

2.) Find all items in the Excel array that are not in the Power BI array. These will be deleted from the Excel spreadsheet.

3.) Find all items common to both arrays. These will have 2 columns updated on the Excel spreadsheet but will otherwise be left untouched.

It seems like the Filter Array function would be a smart way to go, but no matter how hard I try I cannot seem to set it up correctly. It either fails to run, or returns no results, or returns everything without taking anything out. I have tried using item() and putting the unique identifier into an array of its own, and putting the unique identifier into a variable of its own. But nothing seems to work as it should.

Can anyone help me set this up? Thanks!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

DJ_Jamba 2,837 Super User 2025 Season 2 on at

Like (0)

Report
Hi @meskerkj
Maybe someone can do it better than this, but this is how I did it!

Create two dummy arrays:

Create 2 arrays containing Identifier only, via a SELECT map:

Create another array in a compose containing all data from both arrays using union:

sort(union(outputs('Power_BI_Array'),outputs('Excel_Array')),'Identifier')

Create another array of all identifiers only using union:

union(body('Select_Power_BI_Identifier'),body('Select_Excel_Identifier'))

Now it's easy from here:
Identifiers in both Power BI & Excel using Filter:

@and( contains(body('Select_Power_BI_Identifier'), item()), contains(body('Select_Excel_Identifier'), item()) )

Identifiers in Power BI and not in Excel using Filter:

@not(contains(body('Select_Excel_Identifier'), item()))

Identifiers in Excel and not in Power BI

@not(contains(body('Select_Power_BI_Identifier'), item()))

So now we have 3 arrays:
- Identifiers in both
- Identifiers in Power BI but not in Excel
- Identifiers in Excel but not in Power BI

And it's just a case of filtering from the master (Both arrays as 1 array) for the full set of data:

In Both arrays:

@contains(body('Identifiers_in_both'), item()?['Identifier'])

In Power BI and not in Excel:

@contains(body('In_Power_BI_and_not_in_Excel'), item()?['Identifier'])

In Excel and not in Power BI:

@contains(body('In_Excel_and_not_in_Power_BI'), item()?['Identifier'])

Final result:

Was this reply helpful? Yes No
meskerkj 4 on at

Like (0)

Report

Thank you for your help! Unfortunately that did not work. It ran correctly through "In Excel and not Power BI" but when I tried to filter the Master with the selected identifiers it returns no results for any of the 3 "full array" functions.

Was this reply helpful? Yes No

DJ_Jamba

2,837 Super User 2025 Season 2 on at

Like (0)

Report

Create a brand new flow with a manual trigger.
Make sure you're in the classic flow editor.
Copy the code below.

{"id":"f7fec8fa-8516-4ba3-81ef-de7b24e05cab","brandColor":"#8C3900","connectionReferences":{"shared_office365groups":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365groups/connections/637428b819254e3f8b30d9c1053e5342"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/0c064ee9258b4ed9b982f1bd1e90fbcc"}},"shared_sharepointonline_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-f2aa5f43-ba9a-4590-933b-46d5-f44b5ea1"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"3_level_filtering","operationDefinition":{"type":"Scope","actions":{"Power_BI_Array":{"type":"Compose","inputs":[{"Identifier":1,"Fruit":"Orange"},{"Identifier":2,"Fruit":"Apple"},{"Identifier":3,"Fruit":"Banana"},{"Identifier":4,"Fruit":"Grapes"},{"Identifier":7,"Fruit":"Prunes"}],"runAfter":{},"metadata":{"operationMetadataId":"f287858e-3aea-4718-bb7e-168ab3e4ce07"}},"Select_Power_BI_Identifier":{"type":"Select","inputs":{"from":"@outputs('Power_BI_Array')","select":"@item()?['Identifier']"},"runAfter":{"Power_BI_Array":["Succeeded"]},"metadata":{"operationMetadataId":"fe97f9b9-7df1-4414-9ab5-26d09722f331"}},"Excel_Array":{"type":"Compose","inputs":[{"Identifier":3,"Fruit":"Banana"},{"Identifier":4,"Fruit":"Grapes"},{"Identifier":5,"Fruit":"Mango"},{"Identifier":6,"Fruit":"Plum"}],"runAfter":{"Select_Power_BI_Identifier":["Succeeded"]},"metadata":{"operationMetadataId":"5914bcd9-cddb-4b94-bede-3ea23a2553b6"}},"Select_Excel_Identifier":{"type":"Select","inputs":{"from":"@outputs('Excel_Array')","select":"@item()?['Identifier']"},"runAfter":{"Excel_Array":["Succeeded"]},"metadata":{"operationMetadataId":"e40af892-3986-429c-9704-ffaed6ee6ced"}},"Both_arrays_as_1_array":{"type":"Compose","inputs":"@sort(union(outputs('Power_BI_Array'), outputs('Excel_Array')), 'Identifier')","runAfter":{"Select_Excel_Identifier":["Succeeded"]},"metadata":{"operationMetadataId":"c715cc6f-1a33-4b05-acdb-d3a894a0f1b0"}},"All_Identifiers":{"type":"Compose","inputs":"@union(body('Select_Power_BI_Identifier'), body('Select_Excel_Identifier'))","runAfter":{"Both_arrays_as_1_array":["Succeeded"]},"metadata":{"operationMetadataId":"891aa257-965f-421b-b94e-f516e914f187"}},"Identifiers_in_both":{"type":"Query","inputs":{"from":"@outputs('All_Identifiers')","where":"@and(contains(body('Select_Power_BI_Identifier'), item()), contains(body('Select_Excel_Identifier'), item()))"},"runAfter":{"All_Identifiers":["Succeeded"]},"metadata":{"operationMetadataId":"d8fba5b0-9099-43a5-9529-3b9856afdda3"}},"In_Both_-_Full_array":{"type":"Query","inputs":{"from":"@outputs('Both_arrays_as_1_array')","where":"@contains(body('Identifiers_in_both'), item()?['Identifier'])"},"runAfter":{"Identifiers_in_both":["Succeeded"]},"metadata":{"operationMetadataId":"8084051f-6028-4002-8d20-48585d7d5026"}},"In_Power_BI_and_not_in_Excel":{"type":"Query","inputs":{"from":"@body('Select_Power_BI_Identifier')","where":"@not(contains(body('Select_Excel_Identifier'), item()))"},"runAfter":{"In_Both_-_Full_array":["Succeeded"]},"metadata":{"operationMetadataId":"22b94903-c496-4179-93bf-62750cd0aa22"}},"In_Power_BI_and_not_in_Excel_-_Full_array":{"type":"Query","inputs":{"from":"@outputs('Both_arrays_as_1_array')","where":"@contains(body('In_Power_BI_and_not_in_Excel'), item()?['Identifier'])"},"runAfter":{"In_Power_BI_and_not_in_Excel":["Succeeded"]},"metadata":{"operationMetadataId":"bb0fbece-4d02-4000-8480-01419fc9a4d5"}},"In_Excel_and_not_in_Power_BI":{"type":"Query","inputs":{"from":"@body('Select_Excel_Identifier')","where":"@not(contains(body('Select_Power_BI_Identifier'), item()))"},"runAfter":{"In_Power_BI_and_not_in_Excel_-_Full_array":["Succeeded"]},"metadata":{"operationMetadataId":"d74b9610-5326-4c2e-ac0d-70d94f0f02bc"}},"In_Excel_and_not_in_Power_BI_-_Full_array":{"type":"Query","inputs":{"from":"@outputs('Both_arrays_as_1_array')","where":"@contains(body('In_Excel_and_not_in_Power_BI'), item()?['Identifier'])"},"runAfter":{"In_Excel_and_not_in_Power_BI":["Succeeded"]},"metadata":{"operationMetadataId":"e659ed34-d6ca-4922-9090-3358ffd862d0"}}},"runAfter":{},"metadata":{"operationMetadataId":"14cfc357-cac9-4d57-8137-c8ea6185660c"}}}

Click on the plus sign to "Add a new step"
Then switch to the My Clipboard tab.

Paste the code you just copied into that screen and it should give you a scope for you to select to pull into your flow.
That is a working demo doing exactly what you wanted.
So long as the "Identifier" column is the same and stores the same data type in your Excel and Power BI sources, this is the solution.