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

List.Distinct and List.Contains

Hello all,

 

I am attempting to filter a table (Table 1) based on values which are found in another table (Which has only unique values: Table2).

 

Table 1:

Episode #     Week#

Episode 1         5

Episode 2         5

Episode 3         6

 

Table 2:

WeeksToDate
5

 

Currently the source of Table 2 is manual (i.e. Entered via the " Enter Data" function on the PowerBi Query Editor) and I am using the following function on Table 1 to filter it.

=Table.SelectRows(#"Added Custom", each ( List.Contains( #"Table 2" [WeeksToDate], [Week#])))

 

This works great and I have no issues.

 

However, I would like to use the following function (or something similar) to get the week# dynamically (rather than entering manually each week) knowing that Table3[WeeksAired] will always have all the weeks I am interested in - in this case it would not have week 6 :

 

= List.Distinct(#"Table 3"[WeeksAired])

 

Table3:

WeeksAired

5

5

5

5

 

The above function gets the me the value I am intersted in without any issues and I get a table that is seemingly identical to Table2. My issue begins when I replace the reference of Table2 to Table 3 as the filter seems to never end. The process which will normally take ~10-15 seconds now seems to turn forever ( I have yet to see it finish). I was wondering what modificatiosn I could make to have both the dynamic updates as well as the"quick" filtering.

 

 

Note: Apologies if I missed any info/proper etiquette for posting in forums as this is my first post ever

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: List.Distinct and List.Contains

Hi@Mirmiran 

 

Try with List.Buffer

 

Following steps after "Added Custom" step

 

#"Added Custom" = ..............................................
mylist=List.Buffer(List.Distinct(#"Table 3" [WeeksAired])),
Custom1 = Table.SelectRows(#"Added Custom", each List.Contains( mylist, [#"Week#"]))
in
Custom1

Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: List.Distinct and List.Contains

Hi@Mirmiran 

 

Try with List.Buffer

 

Following steps after "Added Custom" step

 

#"Added Custom" = ..............................................
mylist=List.Buffer(List.Distinct(#"Table 3" [WeeksAired])),
Custom1 = Table.SelectRows(#"Added Custom", each List.Contains( mylist, [#"Week#"]))
in
Custom1

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Frequent Visitor

Re: List.Distinct and List.Contains

Fantastic! Thank you!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors