cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichelH
Advocate V
Advocate V

Use distinct on SQL connector query

For my get rows from SQL connector, I would like to use a:

 

SELECT

     DISTINCT([Customer P O No])

    ,[Order no]

    ,[Sold To]

    ,[Sold To Party Name] 

FROM [TESTDB].[dbo].[To Invoice]

 

How to I translate this into any of the filters available under advanced options?

 

Thanks for any comments

 

Michel

1 ACCEPTED SOLUTION

Accepted Solutions
v-yamao-msft
Community Support
Community Support

Hi @MichelH,

 

By using a Distinct function, do you want to return a one-column table that contains the results, with duplicate values removed?

Currently, there is no direct Distinct function can be used in Microsoft Flow. I have achieved this by using variables in flow. Please take the following flow for a reference.

The flow is triggered by a Button.

Get rows from a SQL table.

Initialize variable, set name as TestName, Type as Array.

Add an Apply to each, select Value from Get rows action, within the apply to each, add an action Append to array, set name as TestName, set Value with the following code:

item()?['name']

Under the Apply to each, add action Compose, input the following code to return the items with duplicate items removed:

union(variables('TestName'),variables('TestName'))

Images for your reference:

1.PNG2.PNG3.PNG 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yamao-msft
Community Support
Community Support

Hi @MichelH,

 

By using a Distinct function, do you want to return a one-column table that contains the results, with duplicate values removed?

Currently, there is no direct Distinct function can be used in Microsoft Flow. I have achieved this by using variables in flow. Please take the following flow for a reference.

The flow is triggered by a Button.

Get rows from a SQL table.

Initialize variable, set name as TestName, Type as Array.

Add an Apply to each, select Value from Get rows action, within the apply to each, add an action Append to array, set name as TestName, set Value with the following code:

item()?['name']

Under the Apply to each, add action Compose, input the following code to return the items with duplicate items removed:

union(variables('TestName'),variables('TestName'))

Images for your reference:

1.PNG2.PNG3.PNG 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you Mabel,

 

You suggestion allows me to get the unique/distinct list in an array.

 

I would like to use resulting array to fill a sharepoint list.

How do I access the array elements in a 'apply to each' loop?

In the dynamic context I only see the array as a whole, no reference how to get to an element.

 

I see some references to item() to get the array elements so I tried that, but I'm unable to see what is actually past on to the sharepoint connector, and only get a meaningless error.

unfriendly.jpg

 

All I do is simply add the array to the 'apply to each 2' loop, and take the item() and add it to a sharepoint list, so it seems the array element is not returned as expected.

simple.jpg

 

I also tried using the 'Current Item' to fill both fields but I get the same error.

The array 'Output', send to the 'Apply to each 2' does have a number of values.

 

Do you have an example where we loop through the array and do something with the item(s)?

 

 

Best regards,

 

Michel

 

 

 

I found out how I can get to the arrays elements   :-).

 

I can see I can get the values I want and that they are what I expectArrayElements.jpg

 

The failure of the create item in sharepoint is not related to the issue.

 

Thanks for you help Mabel

 

Best regards,

 

Michel

 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (4,029)