Question: Get Data Advanced SQL Statement using existing PowerBI field

[Anonymous]

Greetings!  I want to filter rows I'm bringing into Power BI Desktop using the advanced option sql query and referencing an already-created field as part of that query. 

If my large database to import/filter has a table called NewTable and I have a table already in PowerBI called SmallTable and both contain a field called CommonID, how do I write the query to Select/Import only rows from NewTable where NewTable[CommonID] is also in SmallTable[CommonID].   I can't seem to find the right syntax to enable Power BI Desktop to reference SmallTable. 

[v-yadongf-msft]

Hi @Anonymous ,

This is Table1:

This is Table2:

Table1 has connected to Power BI by Direct Query mode.

Please try:

SELECT Table2.* FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response. Table1 is part of the SQL database being read in. Table2 is a table ALREADY in PowerBI. The probem is PowerBI is not recognizing the Table2 reference.

[v-yadongf-msft]

Hi @Anonymous ,

On my test, Table 1 is a table already in Power BI. Table 2 is part of the SQL database being read in. 

Please check again.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.