Forum Discussion

DonnaKelly's avatar
DonnaKelly
Regular Visitor
4 years ago
Solved

Filter table based on other filtered table

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    DonnaKelly
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

    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's avatar
    DonnaKelly
    Regular Visitor
    4 years ago

    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.

      • DonnaKelly's avatar
        DonnaKelly
        Regular Visitor
        4 years ago

        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]