cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nileshvk
Helper I
Helper I

Fill dropdown using subquery

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:

nileshvk_0-1643298840834.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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

 

View solution in original post

4 REPLIES 4
Nogueira1306
Super User
Super User

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!

@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
Community Support
Community Support

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

 

@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"

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,297)