Can I join different databases based on the results of a PBI query?

[Anonymous]

Hi all, thanks for looking at my question

So, I have a little database containing some data about some students interacting with a service. And I have a big database containing all student registration data.

I have an import query that brings in the data fields I'm interested in from each database seperately. 1 small dataset and 1 big dataset containing far more data than required.

The problem is size and privacy. I do not need the full dataset from the big database, just the data relevant to students in the little database.

So is there any way to join a table currently in Power BI to another query?

Thanks!

[Anonymous]

Hi @Anonymous,

For now you need to use the merge query and you can hide the table in the dataset so that no one can view your other table.
Or if you can add some filter in the big data set then also that would help.

For now there is no such thing in Power BI to delete the data after the mege join. So I suggest you can hide the data set.

[prateekraina]

Hi @Anonymous,

Yes, you can join tables in Query Editor itself and do away with unrequired data.

Explore the queries option highlighted in above image. You can perform an inner join from your small to big table which will result in less no of rows.

Prateek Raina

[Anonymous]

Thanks for responding Prateek!

Thanks so much for pointing me in the right direction! I've merged the tables now, by doing a left join onto the smaller table, but it looks like the big one is still there in it's entirety, I can't delete it and the /pbix file is the same size it was. 😞

[edhans]

Make sure you aren't loading the big database. Right-click on it in the Power Query window and make sure "Enable Load" isn't checked. Also, in the merged query, only expand the necessary columns you want from the Big Database.

[prateekraina]

@edhans
I think if Enable Load is disabled, then he would miss out on the new rows which May come in future and might be relevant to him if there are matching entries from the small table in it. What’s your take on that?

[edhans]

It will work @prateekraina. If you disable load, when you "refresh all" it will pull all data through the Big Database connection and merge into the MergeQuery, but will then discard whatever isn't needed in the MergeQuery.

I often have 15-20 queries in Power Query to get all of my data, but only 3-5 tables/queries are actually loaded. I make sure the rest are not loaded. It only adds to the file size. I actually have a standard Group in queries called "Reporting Queries" and I only load those. All of my "Source Queries" and "Intermediate Queries" are never loaded.

[prateekraina]

Wow. That’s awesome!! Thanks for letting it know. Appreciate it.

[prateekraina]

Which database are you pulling from?
If its a SQL server database, then you should use DirectQuery instead of Import mode.

[Anonymous]

Hi Prateek

Unfortunately I can't as the 2 tables are from 2 different databases 😞