Forum Discussion
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 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.
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
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 JustThePatientsYou can use that as the table to join with.
= Table.NestedJoin( Source, {"FieldX"}, Table.SelectRows(TableA, each [P] = P), {"FieldX"}, "TableA", JoinKind.Inner )
9 Replies
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
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
How about an inner join of filtered TableA with TableB?
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.
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