Fill dropdown using subquery

[nileshvk]

Hello,

Can someone help me with formula to bind dropdown? Below is the equivalent SQL

Select distinct Col1 from tbl1 where id in (Select tbl1Id from tbl2 where Col2=2)

Sample data & expected output:

[Nogueira1306]

https://docs.microsoft.com/en-US/powerapps/maker/canvas-apps/functions/function-distinct

Use distinct to not repeat and filter the datasource to check where Col2=2

Distinct( Filter( table1, col2 = 2), Col1)

It must be something like that

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[nileshvk]

@Nogueira1306 I am struggling with below portion of SQL as it returns collection of values.

(Select tbl1Id from tbl2 where Col2=2)

  I am looking for something like:

Distinct( Filter( table1 in <collection of Values>), Col1)

[v-bofeng-msft · Accepted solution]

Hi @nileshvk :

I assume there are two tables:

ClearCollect(tbl2,{id:1,Col2:1,tbl1Id:1},{id:2,Col2:2,tbl1Id:2},{id:3,Col2:2,tbl1Id:3},{id:4,Col2:3,tbl1Id:4},{id:5,Col2:3,tbl1Id:5});
ClearCollect(tbl1,{id:1,Col1:"A"},{id:2,Col1:"B"},{id:3,Col1:"C"},{id:4,Col1:"D"},{id:5,Col1:"E"})

Please use this formula to get the result:

RenameColumns(
    Distinct(
        Filter(
            tbl1,
            id in Filter(
                tbl2,
                Col2 = 2
            ).id
        ),
        Col1
    ),
    "Result",
    "Col1"
)

Best Regards,

Bof

[nileshvk]

@v-bofeng-msft , How to eliminate Delegation warning for "in" operator? it says:

"delegation warning: the highlighted part of this formula might not work correctly on large data sets"