cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Using nested list to filter nested table

In my main table [FilterListZC] I have a column [FilterZC] which contains nested lists, and a column [Selected] of nested tables. Each nested table in [Selected] contains a column [TenorCode] I would like to filter with the values in the corresponding list in [FilterZC].

I have tried various solutions but none work. Here is an example of one that does not work:
= Table.AddColumn(FilterListZC, "FilteredZC", each Table.SelectRows([Selected], each List.Contains( (r)=> [FilterZC], [TenorCode])))


nested.PNG

 

Is this possible? What do I need to change? 

Thanks a million for any help!

1 ACCEPTED SOLUTION

Hi @MichaelHalling ,

you can try the following, otherwise please provide sample data:

 

Table.AddColumn(FilterListZC, "FilteredZC", each Table.SelectRows([Selected], (r) => List.Contains( FilterZC], r[TenorCode])))

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @MichaelHalling ,

 

The best person to help you with this is @ImkeF the best M language user in the community.

 

@ImkeF  can you please check this question.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Hi @MichaelHalling ,

you can try the following, otherwise please provide sample data:

 

Table.AddColumn(FilterListZC, "FilteredZC", each Table.SelectRows([Selected], (r) => List.Contains( FilterZC], r[TenorCode])))

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

@ImkeFYou are amazing! That worked! Don't really know what r[TenorCode] does but it solved my problem. Also gotten a few helpful tips from your blog before this so a big THANK YOU for all your work. @MFelix Thanks for caring 🙂

 

PS. You missed a '[ ' in [FilterZC] so for anyone reading this, this is the exact code that solved my problem. 

Table.AddColumn(FilterListZC, "FilteredZC", each Table.SelectRows([Selected], (r) => List.Contains( [FilterZC], r[TenorCode])))

Hi @MichaelHalling ,

very pleased to hear 🙂


And thanks for the correct code, I did mine on top of my head.

 

With regards to r[Tenor]: There is a bit of syntax sugar in the M-language that obfuscates what's really going on:

https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions

https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-...

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors