Filter table based on other filtered table

[DonnaKelly]

Hi,

I have a hard-coded PQ text parameter P.

There's a SQL database Table A, filtered where a field = P  (so, 100 rows becomes 36, for example).  This is good so far.

Here's the problem:

I need to filter SQL database Table B, which does not have a field holding P.  I need to filter Table B based on the filtered content of Table A (so, 271 rows becomes 95, for example, where those 95 in Table B have a FieldX equal to the equivalent FieldX in Table A)).

I can create a Custom Step using the fx button . . . but what is the M code for this next filter step?  I think it is going to be something like

        #"Filtered Rows" = Table.SelectRows(TableB, each [FieldX] = ? )

    in

       #"Filtered Rows"

Am I on the right track?

All help gratefully received.

[watkinnc · Accepted solution]

You really can do what @AlexisOlson advises, but if you'd rather use List.Contains, that's legit as well, like if you don't want the values from Table A (but my first thought was Inner Join as well):

Table.SelectRows(TableB, each List.Contains(List.Buffer(List.Distinct(TableA[FieldX])), [FieldX]))

--Nate

[DonnaKelly · Accepted solution]

Thanks for this code.

I copied it very carefully, substituting the real names for the dummy ones, but failed dismally. 

More by happenstance than skill, I arrived at 

Table.SelectRows(dbo_TableB, each List.Contains(List.Buffer(List.Distinct(TableA[FieldX])), [FieldX]))

I can see what's happening, and why this is doing what I need.

dbo_TableB is referring to the underlying table in the database.  That's good.

TableA is teferring to the filtered TableA which exists after the steps in the M code.   That's also good.

Reading the M code as functional programming, and referring to the docs for each method, makes sense of it all.  I've attached the real code for information.

let
    Source = Sql.Database("redwinghealthserver.database.windows.net", "SMIPHC"),
    dbo_PrimaryPatientObservation = Source{[Schema="dbo",Item="PrimaryPatientObservation"]}[Data],
    JustThePatients = Table.SelectRows(dbo_PrimaryPatientObservation, each List.Contains(List.Buffer(List.Distinct(PrimaryPatient[NHSNumber])), [NHSNumber]))
in
    JustThePatients

[watkinnc · Accepted solution]

You really can do what @AlexisOlson advises, but if you'd rather use List.Contains, that's legit as well, like if you don't want the values from Table A (but my first thought was Inner Join as well):

Table.SelectRows(TableB, each List.Contains(List.Buffer(List.Distinct(TableA[FieldX])), [FieldX]))

--Nate

[DonnaKelly · Accepted solution]

Thanks for this code.

I copied it very carefully, substituting the real names for the dummy ones, but failed dismally. 

More by happenstance than skill, I arrived at 

Table.SelectRows(dbo_TableB, each List.Contains(List.Buffer(List.Distinct(TableA[FieldX])), [FieldX]))

I can see what's happening, and why this is doing what I need.

dbo_TableB is referring to the underlying table in the database.  That's good.

TableA is teferring to the filtered TableA which exists after the steps in the M code.   That's also good.

Reading the M code as functional programming, and referring to the docs for each method, makes sense of it all.  I've attached the real code for information.

let
    Source = Sql.Database("redwinghealthserver.database.windows.net", "SMIPHC"),
    dbo_PrimaryPatientObservation = Source{[Schema="dbo",Item="PrimaryPatientObservation"]}[Data],
    JustThePatients = Table.SelectRows(dbo_PrimaryPatientObservation, each List.Contains(List.Buffer(List.Distinct(PrimaryPatient[NHSNumber])), [NHSNumber]))
in
    JustThePatients

[AlexisOlson]

How about an inner join of filtered TableA with TableB?

[DonnaKelly]

Thanks for this, and I appreciate what you're saying, but I can't do this in SQL, as the hard-coded parameter is a PQ parameter . . . all the filtering needs to be done on the client side.  

Conceptually, what I want to do is JOIN the filtered TableA to TableB (or select from tableB using an EXISTS) and thus generate a filtered TableB based on the TableA subset.  

The way forward looks like some sort of equivalent to an EXISTS like a List.Contains, but I dunno enough M to figure out what to do.

[AlexisOlson]

You can absolutely do an inner join in Power Query without any SQL involved. See the link I provided previously or take a look at this more general documentation about merges (joins):

https://docs.microsoft.com/en-us/power-query/merge-queries-overview

[DonnaKelly]

Ordinary JOINs are easy - no question.

But I have to admit defeat.  I couldn't figure out how to do a JOIN in PQ on the filtered TableA.  I couldn't figure out  -- in the TableB query -- how to reference the data that existed in the TableA query after this step was applied:

= Table.SelectRows(dbo_TableA, each [P] = P)  [where P is the PQ parameter]

[AlexisOlson · Accepted solution]

You can use that as the table to join with.

= Table.NestedJoin(
    Source, {"FieldX"},
    Table.SelectRows(TableA, each [P] = P), {"FieldX"},
    "TableA", JoinKind.Inner
  )

[DonnaKelly]

This is closer to what I originally meant.  It's also what a SQL person like myself would naturally gravitate to. 

Thanks to everyone for a very useful discussion.

[AlexisOlson]

The way wrote it is similar to a subquery expression but you can also do it more like a CTE and define the filtered table as a prior step and then reference that step inside the join.