Use distinct on SQL connector query

[MichelH]

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

[v-yamao-msft · Accepted solution]

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:

 

Best regards,

Mabel Mao

[MichelH]

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.

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.

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

[MichelH]

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 expect

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

Thanks for you help Mabel

Best regards,

Michel