Direct Query - add tables after initial setup

Question

After your initial setup of a Direct Query and picking the 20 or 30 tables, how do you go back and add more tables? Every path I follow in the UI leads me to creating a whole separate data source which then kills the ability to do a direct query.

Also, if there is some place this is documented, please point it out to me. I was unable to find it and I'm pretty good at searching for solutions.

I've searched, read articles that I could find about Direct Query, and now searched this forum for an answer to this. I'm sure I'm missing something obvious because this feature must exist--to no have it would be silly.

That being said, it would behoove the Power BI team to make the methodology for doing this more obvious in the user interface.

v-jianhe-msft · Answer

Hi,&nbsp;When you are using direct query to connect to one datasource, and you would like to add some more table from that datasource, you could go&nbsp; to “Recent Sources” &gt;&gt; then select the data source you are currently using.So you do not need to start from the beginning.&nbsp;Also, “When importing data, it is possible to combine data from multiple sources into a single model, for example, to easily join some data from a corporate SQL Server database with some local data maintained in an Excel file. This is not possible when using DirectQuery. When selecting DirectQuery for a source, it will then only be possible to use data from that single source (such as a single SQL Server database).” See Using DirectQuery in Power BI&nbsp;Best Regards,Henry

techsavvysam · Answer

Thanks for the quick response Henry,&nbsp;OK, I see how that could work because it brings up the list of tables.&nbsp;However...&nbsp;I had originally selected serverN - SandboxDatabase&nbsp;I then had gone into edit query at some point and selected "Data source settings", selected the data source, then clicked on "Change Source..." and changed "Database" to ProductionDatabase.&nbsp; The originally selected tables were still selected and everything worked as I expected.&nbsp;So then all the recent sources show "serverN : SandboxDatabase" as my only option.&nbsp; I select the top of those and it opens the "Navigator"&nbsp;but no tables are selected (I would expect the 4 I was originally testing with).&nbsp;If that doesn't reproduce the issue, not that I also exited Power BI Desktop over the weekend.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-jianhe-msft · Answer

Hi,&nbsp;Are you using two datasources, which are SandboxDatabase and the Production database? If so, it is not allowed to use two datasources using direct query.&nbsp;&nbsp;&nbsp;

techsavvysam · Answer

I understand that Direct Query does not have capability to have more than one data source.&nbsp; In fact, I suspect that's why this short-coming has not been found because probably almost no-one can use direct query (based on reading some topics in this forum if you "hold your face wrong" direct query won't work).&nbsp;I was using the Sandbox database and then I switched to the Production database--I did not add the Production database as an additional datasource.&nbsp;Once again, full steps to get where I am:1) connected to datasource "Sandbox" and added 4 tables2) did some testing3) switched to datasource "Production"4) everything still worked with 4 tables5) tried to add additional tables6) followed steps recommended above7) add table dialog had *NO* tables selected&nbsp;I get that *this* is not a big deal, but imaging having 40 tables out of 70 selected and just wanting to add 1 more.&nbsp;Here's the background of why this needs to work correctly.&nbsp; I'm explaining this because I have found that often people don't understand why I'm asking a question but once they understand my business and usability case for doing this a light bulb comes on for them:&nbsp;I am really beginning to suspect that Direct Query is just not viable (it just has way too many limitations) and I need to see if duplicated most of the data into Power BI over and over and over is viable.&nbsp; This eliminates most of the value of Power BI for one of the projects I'm targeting.&nbsp;Having some unknown code performing some black box extract once a day is what my client already has with an extract for Tableau that kills the system for 8 hours every night so they can run reports on day-old data the next day.&nbsp;The thought of having to add every dimension table back when I decided to add a signle additional fact tables is just not something I want to sign up for.&nbsp;I see a lot of other usability issue with Power BI desktop too.&nbsp; The developers would hate me if I was the product owner.&nbsp; Things like the dropdown for the recent datasources not showing you the full name of the datasource (even when hovering over them)--try figuring out the right datasource when you're using a 70 character long AWS host name.&nbsp;&nbsp;

v-jianhe-msft · Answer

Hi,&nbsp;Thank you for your reply. So basically you are "switching" the datasource when using direct query. I understand it's a pain for this limitation.&nbsp;&nbsp;You could post your idea on https://ideas.powerbi.com/forums/265200-power-bi-ideas&nbsp;and the Product team will keep improving relative features.&nbsp;&nbsp;

Forum Discussion

Direct Query - add tables after initial setup

5 Replies

Related content

Direct Lake Model - Calculated Column/Tables

SQL initialization error

Bi-Directional filtering

Replace import table with direct query table

Fetch new tables in direct lake