Retrieve records from a table based on the results of another table

[iabarraganc]

Greetings to all

I have two tables in the powerBI file. The first table contains the detail of what needs to be displayed (table1). The second table (table2) contains a single field, through which the details of table1 will be searched.

I clarify that the data in table 2 is dynamic (it can be 1 record or 100 records, depending on the needs).

In SQL code I solve the situation this way:
select id_lookup, field1, field2, field3 from table1 where id_lookup in (select id from table2)

This is needed so that only the necessary records from Table 1 are loaded (as it has millions of records) and to lighten the load on PowerBI.

Because of this I cannot import the whole table1. How can this be done? I have searched for help through M language but what I find tells me that I must load the whole table1, which would not be efficient.

Thank you for your help.

[edhans · Accepted solution]

If this is in SQL, it will not load the whole table.

1. Load tables 1 and 2 into Power Query.
2. Merge as needed. Sounds like you need an Inner Join on the ID column - i.e. only pull records from table 1 and 2 where they both have the same ID.
3. Expand table 2 after the join.
4. Make sure Table 2 is not set to "Enable Load" into the Power BI model. Right-click on it and disable the load.
5. Table 1 will load into DAX with only the records that matched in table 2.

All of that will fold back to the SQL server, so 100% of the work is done in SQL. Only the transfer of the final recordset will happen in Power BI. Many subsequent transformations may fold too, like column renames, grouping, many new columns, etc. Power Query and SQL Server generally work really well together.

[iabarraganc]

Hello
Precisely, I set the example in SQL. But the data is in another source (JSON and CSV)

[edhans]

Then it will have to load all of the records to see what it needs. That is how it works. Either your server does it (SQL Server, Oracle, etc.) via query folding, or Power Query does it when there is no server (Excel files, CSV files, etc.) It will not keep the records. It will only keep what it needs. But it might have to read 10,000,000 records to return 10,000, and only the final 10,000 would actually load into Power BI's DAX model.

There is no other way to do it. SOMETHING has to process the records, right?

[iabarraganc]

Thank you @edhans for all your help. I did the test and it worked well. Since I have to work with dataflows, then this is the right choice.

Complementing your answer, I found this video that corroborates what you explained to me: Append vs Merge in Power BI and Power Query 

It is incredible the support that this community gives us. An excellent job!!!

[edhans]

Great @iabarraganc - glad you found the solution you need. DataFlows are very powerful and great for this kind of thing. Let the DataFlow do a lot of the work, then the Power BI report refreshes very quickly.