cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Chris99 Member
Member

Add missing tables from data source to existing dataset

Hello,

 

Is it possible to add tables from a data source (SQL Server) that weren't initially imported into a dataset?

 

I thought that in the Desktop I had seen a view that looked like the Navigation page when you initially select Enter Data, that allowed you to tick or untick tables from the existing dataset, but I might have imagined it Smiley Frustrated

 

Thanks,

 

Chris

2 ACCEPTED SOLUTIONS

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

when you first connect and select multiple tables each table is actually created separately, the easy way to edit it is to go to Query editor 

 

the easiest way to add new table is to add new data source (no need to go to Query Editor at all). I think you will see all the tables there, so you could possibly load the same table twice, but that's easy to fix
Capture.PNG

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

I think so, I didn't use the data gateways much
you can see the dependencies in the query editor, inthe View Tab
Capture.PNG

I expect you to see your DW at the top with arrows pointing to multiple tables (regardless whether they were created in the begining or added later)

 

You can review the M code that got generated for different tables in Advanced Editor in the Home tab, so you can compare it for  the original and added tables - top 2 rows should be identical (if it's all same database)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

you just create a new query as a connection to SQL Server and you will have the same interface again

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Chris99 Member
Member

Re: Add missing tables from data source to existing dataset

@StachuHi. How do you create a new one? Presumably it is in Edit Queries, but I can only see how to modify an existing one.

 

Is there no easy way to see which tables I haven't imported?

 

Chris

Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

when you first connect and select multiple tables each table is actually created separately, the easy way to edit it is to go to Query editor 

 

the easiest way to add new table is to add new data source (no need to go to Query Editor at all). I think you will see all the tables there, so you could possibly load the same table twice, but that's easy to fix
Capture.PNG

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Chris99 Member
Member

Re: Add missing tables from data source to existing dataset

Thanks for replying. Does this mean I would have two datasets, as forum posts suggest this can increase refresh issues?

 

Thinking about it, it just sounds easier to duplicate an existing query, and then modify it to import from a different table. It does seem like a bit of a long winded way though. I might make a suggestion on Power BI ideas.

 

Chris

Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

both ways have the same result. M query with syntax similar to his one:

let
    Source = Sql.Databases("server addres"),
    #"DW" = Source{[Name="DW address"]}[Data],
    dimTable = #"DW"{[Schema="dbo",Item="TableName"]}[Data]
in
    dimTable

whether you will do it by copying existing query, or adding "new" data source etc. it doesn't matter, it's just a different way of achieving the same outcome

PowerBI will treat each table as a separate query in the same dataset

if you work with multiple environments I would suggest to split the process a bit to something like this
Query SQL_Connection (connection to SQL server that is then referenced by all the tables)

let
    Source = Sql.Databases("server addres"),
    #"DW" = Source{[Name="DW address"]}[Data]
in
    #"DW"

Dimension1 (referencing previous query)

let
    Source = SQL_Connection,
dimTable1 = #"DW"{[Schema="dbo",Item="TableName"]}[Data]
in
dimTable1

 

Dimension2

 

let
    Source = SQL_Connection,
dimTable1 = #"DW"{[Schema="dbo",Item="TableName"]}[Data]
in
dimTable2

 that way you only need to update the server address in one query (e.g. when switching from DEV to QA or PROD), and whenever you need to add a new table you just reference SQL_Connection and navigate to the new table

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Chris99 Member
Member

Re: Add missing tables from data source to existing dataset

@StachuAah, does that mean if I do a second Get Data whick looks at the same data source as my existing dataset, then the new tables are added to the exisitng dataset exactly the same as if they were included in the Get Data initially ? If so, then for a scheduled refresh through a data gateway will all the queries run in the same manner, irrespective of the number of Get Datas that were run when creating the dataset?

 

Thanks again,

 

Chris

Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

I think so, I didn't use the data gateways much
you can see the dependencies in the query editor, inthe View Tab
Capture.PNG

I expect you to see your DW at the top with arrows pointing to multiple tables (regardless whether they were created in the begining or added later)

 

You can review the M code that got generated for different tables in Advanced Editor in the Home tab, so you can compare it for  the original and added tables - top 2 rows should be identical (if it's all same database)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Chris99 Member
Member

Re: Add missing tables from data source to existing dataset

@StachuThanks. I'll accept this as the solution Smiley Happy

Stachu Super Contributor
Super Contributor

Re: Add missing tables from data source to existing dataset

glad to help 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)