Power Query - Table.SelectRows

Question

I have two tables/queries in Excel. One table is a Group dimension that lists out each Group along with some metadata about each group. The next table is the fact table that has monthly information for each group. The tables look like this:

Table1

GroupName       Location     GroupID   
Even Steven     New York     04952
BillyBobs       Texas        04035
Nunya           California   04859

Table2

GroupID     Description    SalesVolume
04952       Even Steven    59003
04035       BillyBobs      98390
04859       Nunya          50194

For all intents and purposes GroupName and Description are different names for the same thing (GroupName = Description)

What I am trying to do is set things up so that when I filter Table1 on GroupName, Table2 will be filtered as well. So if I filter out Even Steven in Table1, then Table2 will only show BillyBobs and Nunya.

My current Power Query script looks like this (note: I have another table called "Query", that is just an unfiltered SELECT * FROM dbo.Table, I'm using this as the source for Table2. Table1 comes from another table in the same DB):

let
    Source = Excel.CurrentWorkbook(){[Name="Query"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Year", type date}}),
in
    #"Removed Columns"

I believe that I need to add a step similar to the following example found online, but I cant figure out how to say I only want rows that are visible on the Group table.

Table.SelectRows(Sales_Customers, each List.Contains(Query, [CustomerCategoryID]))

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-27T15:54:13

First, we need some way for PowerQuery to know whether or not a row has been filtered or not. One approach is to use the SUBTOTAL function. Add a new Subtotal column to your Table1 with the following formula:

=IF(SUBTOTAL(104,[@GroupID])=[@GroupID], "Visible", "Hidden")

Now merge Table1 with into Table2 matching Table1[GroupName] and Table2[Description].

Expand out just the Subtotal column and then filter just the Visible rows.

Power Query - Table.SelectRows

1 Answers