How to add more tables from the same database - in a friendly way?

[Anonymous]

We have a model that connects to an SQL database. Initially, 4 tables have been added into the model, but now database contains more tables that also have to be included in the model.

How do we do that in an efficient and user-friendly way, and without creating a duplicate database connection within the model?

We have considered following options, none is fine:

1. Go to the "Edit Queries", in the context menu below the queries list on the left choose "New query-More...", select the data source type we need and enter the settings. Problem is: this creates a duplicate data source entry within the model, which is of course unacceptable.

2. "New source" from ribbon, then proceed as usual. Same problem with duplicate data source entry.
3. "Edit queries", then "Duplicate" an existing query through its context menu, then "Advanced editor" to change its settings - e.g. point to a different table. Not user friendly and high risk of errors.

What we consider the optimal scenario is to something like this: click "SOMEWHERE", get the list of data sources defined within the model. Select the datasource, click e.g. "Manage connected queries". See the list of tables similar to what you see when connecting to a database source for the fist time (where you can checbox required tables). Checkbox the tables that we want to add to the model, done - then transform their data as usual.

Is there no such obvious thing available?

[eshehata]

You can duplicate one of the existing tables as follows:

1. right click on the table and choose duplicate.

2. change the name "Peoperty Name" to match the new table name.

3. go to the second list in query "navigation" and choose the new table.

4. Refresh and apply.

[Shivam_Kathpal]

Hi, @v-lili6-msft and @Anonymous, did you find out the correct way to add more tables from a same sql data source in power bi.

Actually I am also looking for same problem, if you have found the solution, please share.

Thanks & Regards

Shivam Kathpal

[v-lili6-msft]

hi  @Anonymous 

For your requirement, you could try these two ways as below:

1. Go to "Recent Sources", then choose the SQL database and connect to it again.

2. Copy the code in "Advanced editor" for one table that already exists in edit queries

For example:

Then create a blank query( New Source->Blank Query),

and paste the code into the "Advanced editor" of this blank query, just replace the name of Item of this code.

For example:

let
    Source = Sql.Databases("WS-WUXIPC-5132"),
    TestGatewayDocs = Source{[Name="TestGatewayDocs"]}[Data],
    dbo_Category = TestGatewayDocs{[Schema="dbo",Item="Category"]}[Data]
in
    dbo_Category

let
    Source = Sql.Databases("WS-WUXIPC-5132"),
    TestGatewayDocs = Source{[Name="TestGatewayDocs"]}[Data],
    dbo_Category = TestGatewayDocs{[Schema="dbo",Item="Product"]}[Data]
in
    dbo_Category

Regards,

Lin

[Anonymous]

Hello. Option #1 that you've suggested is unavailable if you did not add any sources recently.
Option #2, while understandable, does not pass the "user friendly" criteria.

However, I now have a more important question at hand. Does powerbi model at all store data source connections within a model in a centralized way? It seems that it does not, and instead parameters for each data source are stored as a part of a specific query - editable in an "Advanced Editor".

Is that correct that storage of data source connections within the model is not normalized?

[v-lili6-msft]

hi @Anonymous 

For each query, they have their own data source connections. you could get it in Advanced editor.

Regards,

Lin

[Anonymous]

This is a rather questionable design. What if we have, say, 17 queries using the same data source, then we need to change data source configuration - for example, because database has been moved to another server?

We will then need to make 17 independent changes, once per each query, and to manage such changes properly, we need to somehow remember that it's the same, single datasource entity and not 17 separate sources.

[v-lili6-msft]

hi  @Anonymous 

Yes, you may need to update 17 queries one by one.

Regards,

Lin