Combine different database tables

[Analitika]

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

[amitchandak]

@Analitika , You mean append? refer

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Analitika]

I mean Table.Combine

[amitchandak]

@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?

[Analitika]

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

[BA_Pete]

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

[Analitika]

@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.

[BA_Pete · 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

[amitchandak]

@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.

[Analitika]

 already tryied, it provide errors as no linking table exists

[amitchandak]

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