Merge SQL databases in Excel PowerPivot

[marboe]

Good day,

I have 4 databases in SQL Server Express. They all have the same columns. Each database contains 2.000.000 rows.

I would like to make queries and/or pivottables from PowerPivot on all 4 databases at once.

I tried to follow these instructions:
http://accessanalytic.com.au/combine-mu ... wer-query/

If I do the same for 2 databases (instead of 2 tables as in the link),
I get this code in the Advanced Editor (step 4 from the link):

======================================================================
let
Source = Sql.Database("DESKTOP-1\SQLEXPRESS", "2016-Apples-Part3"),
dbo_AppleData3 = Source{[Schema="dbo",Item="AppleData3"]}[Data],
#"Appended Query" = Table.Combine({dbo_AppleData3, dbo_AppleData3})
in
#"Appended Query"
======================================================================

My seconds database: is ("DESKTOP-1\SQLEXPRESS", "2016-Apples-Part4"),
dbo_AppleData4

My problem is that the Table.Combine statement in my case does not combine Source, it does combine dbo_AppleData3 which is a table in a database.

Does anyone know how to modify the code so that it does Table.Combine for dbo_AppleData3 in database 2016-Apples-Part3 and dbo_AppleData4 in database 2016-Apples-Part4 ?

thank you!

Mike

[MattAllington]

If your source is sql server and you want to load the 4 tables to power pivot, presumably as appended tables, I recommend you write sql code instead and then import from there. The sql would be something like this.

Select *
From table1

Union

Select *
From table2

Union

Select *
From table3

Union

Select *
From table4

[marboe]

Hello Matt,

Thank you for your answer.

I have 4 seperate databases in SQL Server Express (due to the limit of 10 GB per database).
Each database contains 1 table. I will try if it is possible to make a query for 4 different databases.

I was hoping I could reach all DB's with one query from PowerPivot (without loading those into a sheet because of the limit of 1.000.000 rows). I suppose modification of the query would be less flexible because you have to modify it in 4 rows every time. If I make a query for all records i think it is too big because of the limit of 10GB.

I will do some testing and let you know.

Thanks.

[AndVGri]

Hi
Matt wrote about sql query for solution of your task.
Sql text for two different database of single server is

Code: Select all

let
    source = Sql.Database(
       "DESKTOP-1\SQLEXPRESS",
       "2016-Apples-Part3",
       [Query=
"Select * From 2016-Apples-Part3.dbo.AppleData3
Union All
Select * From 2016-Apples-Part4.dbo.AppleData3"
   ])
in
    source

Regards,

[marboe]

Hi And,

Thank you for answer. For some reason I do not succeed to replicate the situation in step 4 from the link. I should have saved this particular situation. I will try again in the coming days and let you know if I can replicate the situation and could integrate your code.

Thanks

[marboe]

THIS MESSAGE ABOVE FROM Macros IS SPAM.

Moderator can you delete this please.

Thank you.

[marboe]

Hi And, Hi Matt,

I have tried to find a workaround.

I have made a script in Excel VBA with a query to import the records I need from the SQL-databases.

I have 12 databases and I do 12 queries by a loop in VBA.
(Within SQL I made a count first so that every query does not contain > 1.000.000 (the maximum rows in Excel).)

This works perfect with the ADO-query from this website:
https://www.excel-sql-server.com/excel- ... ng-vba.htm

After I have run the loop, I have a workbook with 12 sheets, all with between 700.000 and 1.000.000 rows. All with similar headings.

I would like to make a pivottable with Power Pivot from those 12 sheets with data.
I have added all sheets to the datamodel, after that I try to make a pivottable by clicking Powerpivot > Home > Pivottable.
If I click a certain columnname to use as a Pivot table field, my tables are still separated in the pivot table output.
Is there an option so that Powerpivot sees all 12 sheets as one table?

Thank you!