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

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors