One M query being dependent on data from parallel M query

Question

I have these two tables in SQL:

CREATE TABLE X(
    "User"   VARCHAR(5),
    "Amount" INT
);
INSERT INTO X
    values
        ('Phil', 10),
        ('jon', 4),
        ('jo', 5);


CREATE TABLE Y(
    "User"   VARCHAR(5)
);
INSERT INTO Y
    values
        ('Phil'),
        ('jo'),
        ('paul'),
        ('jon');

I import both into Power BI via these two M scripts:

let
    Source = Sql.Database("serverX", "dbFoo"),
    dbo_X = Source{[Schema="dbo",Item="X"]}[Data]
in
    dbo_X

and

let
    Source = Sql.Database("serverX", "dbFoo"),
    dbo_Y = Source{[Schema="dbo",Item="Y"]}[Data]
in
    dbo_Y

Is it possible to amend the Y M script so that it only imports usernames that have been imported by the X script? So in the above example, I'd like the second script to check if a user is in X and if it isn't, such as "Paul" then it won't appear in Y

Answer 1

There are several ways to do this.

---

Merge the queries with an inner join

Choose Merge Queries under the Home tab and select Inner as the Join Kind:

This should result in the following table:

Select the X column and use Remove Column to just get the User column.

---

Filter Y using Table.SelectRows

Add a new line to your Y query M code as follows:

let
    Source = Sql.Database("serverX", "dbFoo"),
    dbo_Y = Source{[Schema="dbo",Item="Y"]}[Data],
    YTable = Table.SelectRows(dbo_Y, each List.Contains(X[User], [User]))
in
    YTable

---

Do the filtering on the server side

Use a custom SQL query when you load the table:

let
    Source = Sql.Database("serverX", "dbFoo",
                          [Query = "select * from Y inner join X on Y.User = X.User"])
in
    Source