Member Filter based on List of Values

Question

I am trying to use a member filter when filtering a query based on a list of values in an excel worksheet. The list is housed in a table and is variable, meaning it can range from 1 to several items. I have found coding that can member filter based on one specific cell, but this has proven more difficult. The only list filters I have found use a text filter, which significantly slows down the query time.

Below is the code for a member filter when referring to one specific cell. The cell is B1, and I named it ProNum. ProNum stands for Project Number.

= Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([Projects.Project Number]) = "[Projects].[Project Number].&[" & Text.From(ProNum) & "]" meta [DisplayName = ProNum])

Below is a text filter based on a list of values. I first created a list from the excel table. I then used a text filter based on the Power Query list.

= Table.SelectRows(#"Removed Columns", each (List.Contains(ListOfLocGP,[LocGP])=true))

For the above coding to work, a person has to enter into an excel table the office location and global practice they plan on using for each person on a job (i.e. Seattle and Accounting). After that is complete, the queries are refreshed and the list is condensed down into unique items. The final format is Loc.GP (i.e. SEA.ACT). The list is then pulled into a another query and used as a filter on a custom column I created that combined location and global practice. The query shows employees per each location and global practice. I plan on pulling this information into a data validation drop down list that shows only the relevenant names per Loc.GP, considerably narrowing down the list of applicable employee names. Once a name is chosen, that employee's bill level is then listed by their name which is used to calculate project revenue.

Any help would be greatly appreciated!

jennratten · Answer

Hello - this is how you can filter a column based on a list of values.&nbsp; The example below is a list of numbers and a column of numbers.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE0AAIlHSUgrRSrAxQwggoYwQRMoALGMAFTqIAJTMAMKmAKEogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [accountNumber = _t, #"Converted Totals" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"accountNumber", Int64.Type}, {"Converted Totals", Int64.Type}}),
    varList = {410000,420000},
    FilteredTable = Table.SelectRows ( #"Changed Type", each List.Contains ( varList, [accountNumber] ) )
in
   FilteredTable
My list variable:

&nbsp;
&nbsp;
&nbsp;BEFORE

AFTER

&nbsp;

mjohnston0209 · Answer

jennratten&nbsp;, I understand the second part of your code since it is very similar to what I did already. Will you please walk through the first part? Also, the varlist is very exact. Does it update automatically when variables are used?Below is my output.All names and GP/Locs are fictional.

jennratten · Answer

Hi&nbsp;mjohnston0209&nbsp; sure thing.&nbsp; This top portion is just the code that is generated when you add a table to Power Query by typing it in.&nbsp;

&nbsp;
My varList is specific because I was just making up an example to show you.&nbsp; It could be built with variables, though, using many different methods.&nbsp; If you are using Excel and trying to harvest the list from file data, you can do so with a named range, table column or sheet name and target range.&nbsp; If you are trying to use a list made up of values in GPLoc from your screenshot, you could do so like this:
&nbsp;
&nbsp;
let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    GPLoc = Source[GPLoc]
in
    GPLoc
&nbsp;
Line 1 gets the table named MyTable from the current Excel workbook.&nbsp; Then Line 2 drills the table down to the GPLoc column, as a list.&nbsp; You could also do it in one step like this:
Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content][GPLoc]