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
Analitika
Post Prodigy
Post Prodigy

Combine different database tables

I have two identical databases for separate company

I need combine (not join!!) each same_name tables in this two databases

 

How i can do that in M?

 

Now for one database i do something like

 

let
Source = Sybase.Database(SelectServer, SelectDatabase),
DBA_view_DidknObj = Source{[Schema="DBA",Item="view_DidknObj"]}[Data]
in
DBA_view_DidknObj

 

1 ACCEPTED SOLUTION

Hi @Analitika ,

 

The 'otherSteps' lines were just placeholders to represent further transformation steps if you needed to do them, such as 'Changed Types' etc. They are not necessary but show that you must use the last step in each Source segment in the Table.Combine() function.

 

The data source privacy levels are something that you need to resolve at your end. If these sources are already set up in Power Query, you can try going to Data Source Settings and check that both data sources have the same privacy levels i.e. None, Private, Organisational, or Public.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

I mean Table.Combine

@Analitika , that should be the same as append option in menu?

Can you share a few lines of sample data and sample output in table format?

If i do like that i will have 4 database sized in one pbix file, but i want to have 2 database sized file

 

1st database  = 700 Mb

let
Source = Sybase.Database(SelectServer, SelectDatabase1),
DBA_view_DidknObj = Source{[Schema="DBA",Item="view_DidknObj"]}[Data]
in
DBA_view_DidknObj

 

2st database  = 700 Mb

let
Source = Sybase.Database(SelectServer, SelectDatabase2),
DBA_view_DidknObj = Source{[Schema="DBA",Item="view_DidknObj"]}[Data]
in
DBA_view_DidknObj

 

Combined database  = 1400 Mb

let
Source = Table.Combine({#"DBA 1", #"DBA 2")"})
in
Source

 

Total = 700+700+1400 = 2800 MB

I need only 1400 MB

Hi @Analitika ,

 

You can do this in a single query so you're not holding all the tables. Something like:

 

let
  Source1 = Sybase.Database(SelectServer, SelectDatabase1),
  DBA_view_DidknObj1 = Source1{[Schema="DBA",Item="view_DidknObj"]}[Data],
  otherSteps1 = otherSteps,

  Source2 = Sybase.Database(SelectServer, SelectDatabase2),
  DBA_view_DidknObj2 = Source2{[Schema="DBA",Item="view_DidknObj"]}[Data],
  otherSteps2 = otherSteps,

  Append = Table.Combine( {otherSteps1, otherSteps2} )
in
  Append

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete 

Expression.Error: The name 'otherSteps' wasn't recognized. Make sure it's spelled correctly.

Expression.Error: The import otherSteps matches no exports. Did you miss a module reference?

Formula.Firewall: Query 'DBA view_DidknObj' (step 'Append') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

 

Hi @Analitika ,

 

The 'otherSteps' lines were just placeholders to represent further transformation steps if you needed to do them, such as 'Changed Types' etc. They are not necessary but show that you must use the last step in each Source segment in the Table.Combine() function.

 

The data source privacy levels are something that you need to resolve at your end. If these sources are already set up in Power Query, you can try going to Data Source Settings and check that both data sources have the same privacy levels i.e. None, Private, Organisational, or Public.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@Analitika , I am confused now. If you are looking file size. That the size is the first two tables + one combined table.

 

Created a copy of pbix file and try to delete the source table after combining it. and check. Please keep backup.

 already tryied, it provide errors as no linking table exists

@ImkeF , what is the best way to comine and have a minimum size?

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.