Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TechSavvySam
New Member

Direct Query - add tables after initial setup

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.

 

 

5 REPLIES 5
v-jianhe-msft
Resolver II
Resolver II

Hi,

 

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  to “Recent Sources” >> then select the data source you are currently using.

1.PNG

So you do not need to start from the beginning.

 

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

 

Best Regards,

Henry

Thanks for the quick response Henry,

 

OK, I see how that could work because it brings up the list of tables.

 

However...

 

I had originally selected serverN - SandboxDatabase

 

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.  The originally selected tables were still selected and everything worked as I expected.

 

So then all the recent sources show "serverN : SandboxDatabase" as my only option.  I select the top of those and it opens the "Navigator" but no tables are selected (I would expect the 4 I was originally testing with).

 

If that doesn't reproduce the issue, not that I also exited Power BI Desktop over the weekend.

 

 

 

 

 

Hi,

 

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. 

 

 

I understand that Direct Query does not have capability to have more than one data source.  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).

 

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.

 

Once again, full steps to get where I am:

1) connected to datasource "Sandbox" and added 4 tables

2) did some testing

3) switched to datasource "Production"

4) everything still worked with 4 tables

5) tried to add additional tables

6) followed steps recommended above

7) add table dialog had *NO* tables selected

 

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.

 

Here's the background of why this needs to work correctly.  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:

 

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.  This eliminates most of the value of Power BI for one of the projects I'm targeting.

 

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.

 

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.

 

I see a lot of other usability issue with Power BI desktop too.  The developers would hate me if I was the product owner.  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.

 

 

Hi,

 

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. 

 

You could post your idea on https://ideas.powerbi.com/forums/265200-power-bi-ideas and the Product team will keep improving relative features. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.