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
Merge SQL databases in Excel PowerPivot
-
- Posts: 928
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Merge SQL databases in Excel PowerPivot
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
Select *
From table1
Union
Select *
From table2
Union
Select *
From table3
Union
Select *
From table4
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Re: Merge SQL databases in Excel PowerPivot
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.
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.
Re: Merge SQL databases in Excel PowerPivot
Hi
Matt wrote about sql query for solution of your task.
Sql text for two different database of single server is
Regards,
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,
Re: Merge SQL databases in Excel PowerPivot
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
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
Re: Merge SQL databases in Excel PowerPivot
THIS MESSAGE ABOVE FROM Macros IS SPAM.
Moderator can you delete this please.
Thank you.
Moderator can you delete this please.
Thank you.
Last edited by marboe on Sat Apr 22, 2017 4:38 am, edited 3 times in total.
Re: Merge SQL databases in Excel PowerPivot
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!
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!
Who is online
Users browsing this forum: No registered users and 6 guests