how do I add (and not replace) data into Power Pivot ?

ExcelTeen

New Member
Joined
Jun 18, 2015
Messages
10
Hello,
a simple question about Power Pivot, that I am not very familiar with …
I would like to know how I can add (append ?) - and not replace - data in a Power Pivot model.
In a nutshell, I analyze 4 great regions (Europe - America - Asia - Africa), each in an Excel file requiring lots of operations that I automatized thanks to VBA
The sum of the 4 regions is over Excel limit, so I am thinking of putting them in Power Pivot
But when I try to import the 4 regions, the existing data gets replaced so that I only have the last imported region … :oops:
How can I append and not replace the existing data ?
In advance, thank you for your help

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,714
use APPEND in PowerQuery (Get&Transform) then add to DataModel if necessary
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,714
in short:

  1. I assume all four files have the same structure and format
  2. load them into PowerQuery
  3. use Append
  4. do what you want with the result :)
 

ExcelTeen

New Member
Joined
Jun 18, 2015
Messages
10

ADVERTISEMENT

Hi Sandy,
Yes, all files have the same structure.
I downloaded Power Query add-in and know how to append data from Table 1 to Table 2 in Excel, but not in Power Pivot.
When I try with Power Query to append, a window gets open, inviting to select a primary table and the table to be appended to the primary table, but
with no possibility to select … In fact, the two tables are not in the model but in the original file.
What do I do wrong ?
Feel free to describe the relevant procedure as you would do it to a kid because I tried so many things without success ...
I prepared a simplified example including 2 files but I just discovered that it is impossible to load files in this forum !
So it is a bit complicated to explain where my problem is. Nevertheless, the structure was the following :
Sources.xlsx contains the four regions (4 tables) I want to put together in the model
MyModel.xlsx contains the model (and unfortunately as described I don't know how to have a unique table with the different regions)
In advance, thanks for your help

<tbody>
</tbody>
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,714
use OneDrive, Google Drive or any other to share file(s) and post link(s) here

but I think this is not necessary
if you've all tables loaded into PQ then from the ribbon use Append. You'll see new window with all tables then add tables you want to append from left to the right
It will give you new table called Append1 (you can change it if you want).
I forgot to say before each table need ID : column Region with Europe, Asia, America & Africa (do that before Append) something like:

Region
Europe
Europe
Europe
because in the future you'll be know which data is for which region

if you want add this table Append1 to DataModel use Close&Load to... then select Connection Only (should be selected by default) and at the bottom check Add to data model

hope it's clear :biggrin:
 
Last edited:

ExcelTeen

New Member
Joined
Jun 18, 2015
Messages
10

ADVERTISEMENT

Sandy,
I got it !!!
I finally understood my error, because I did not begin with the right file.
I had to take the file including the 4 tables and from it apply the PQ procedure …
Also my fault because I had american documentation (in English) and I use a French version of PP
So instruction names are somewhat different.
Also thanks for the tip about the regions but I always do so

Again, thank you very much for your help
Seems trivial but for me, absolute beginner in PQ, it is the achievement of the day !
 

ExcelTeen

New Member
Joined
Jun 18, 2015
Messages
10
Hi Sandy,

I didn't think of having a look in MS litterature : you're right, it's a good source !
Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top