Comparing values

(0) Share

Report

Posted on by astockli Microsoft Employee

I am performing a kusto query to obtain data from one source and an ADO query to get data from a different source. I want to compare the idnumber from the Kusto query to make sure there are no duplicates in the ADO data. What would be the best way to do that? I've tried using arrays but it doesn't seem to be working. Any ideas?

Categories:

Using flows

All responses (3)

Answers (0)

Sort by

Suggested answer

Garima_PowerPlatform 170 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi

1. Retrieve Data from Kusto and ADO Queries

Kusto Query

Perform a Kusto query to extract the idnumber values.

KustoQuery | project idnumber

ADO Query

Use ADO REST API to get data and extract the idnumber values.

json

GET https://dev.azure.com/{organization}/{project}/_apis/wit/workitems?api-version=6.0

2. Extract Unique idnumbers

After fetching the data from both sources, extract the idnumber into arrays.

Kusto Extraction

YourKustoQuery | project idnumber

ADO Extraction

json

[ { "id": 1, "fields": { "idnumber": "123" } }, { "id": 2, "fields": { "idnumber": "456" } } ]

3. Compare for Duplicates

Compare the idnumber arrays using Power Automate.

Using Power Automate:

Extract idnumber from Kusto:

YourKustoQuery | project idnumber

Extract idnumber from ADO:

Json

[ { "fields": { "idnumber": "123" } }, { "fields": { "idnumber": "456" } } ]

Use Apply to Each:

Use Apply to Each to iterate through each idnumber from the Kusto query and check if it exists in the ADO results.

Compare for Duplicates:

kusto

union (KustoIdNumbers) intersect (ADOIdNumbers)

This will output common idnumbers between Kusto and ADO.
astockli Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Garima_PowerPlatform How are you doing this in Power Automate?
Suggested answer

Garima_PowerPlatform 170 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi there are different ways to achieve this functionality if you are looking for Power Automate here are steps:

Use Power Automate if the process needs automation within Microsoft's ecosystem.

Use Kusto for query-based comparison if data can be integrated.

Use Python or PowerShell for more control and flexibility in processing.

Power Automate Flow to Compare idnumber

1. Trigger the Flow

Choose a suitable trigger, such as:

A button in Power Apps.

Scheduled flow.

HTTP request trigger (if called programmatically).

2. Retrieve Data

Run the Kusto Query:

Use the "Run Query and List Results" action from the Azure Data Explorer connector.

Parse the results to extract the idnumber values into an array:

json

[ { "idnumber": "123" }, { "idnumber": "456" }, { "idnumber": "789" } ]

Query ADO Data:

Use the HTTP with Azure DevOps connector or the "Send an HTTP request" action.

Query the ADO API to get work item details.

Parse the response to extract the idnumber values:

json

[ { "idnumber": "123" }, { "idnumber": "999" } ]

3. Compare the Arrays

Initialize Two Variables:

KustoIDs: Store the idnumber array from the Kusto query.

ADOIDs: Store the idnumber array from the ADO query.

Apply to Each Loop (Iterate over Kusto IDs):

Add an "Apply to Each" action.

Use the KustoIDs array as the input.

Condition to Check for Duplicates:

Inside the loop, add a Condition action:

Condition: Check if the current idnumber from KustoIDs exists in ADOIDs.

Expression:
contains(variables('ADOIDs'), items('Apply_to_each'))

Handle Duplicate Matches:

If Yes (duplicate found):

Append the duplicate to a new array (e.g., DuplicateIDs).

If No: Do nothing.

Quick overview

Trigger: Manual, Scheduled, or Power Apps.

Query Kusto: "Run Query and List Results" (Azure Data Explorer).

Query ADO: Use "Send an HTTP Request" or "List Work Items" (Azure DevOps).

Initialize Variables: For KustoIDs and ADOIDs.

Apply to Each: Iterate over KustoIDs.

Condition: Check if current idnumber exists in ADOIDs.

Action: Append duplicates to DuplicateIDs array.

Output: Log, send, or return duplicate results.