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

[ExcelTeen]

[TABLE="width: 1021"]
<tbody>[TR]
[TD]Hello,[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]a simple question about Power Pivot, that I am not very familiar with …[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I would like to know how I can add (append ?) - and not replace - data in a Power Pivot model.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
[TR]
[TD]The sum of the 4 regions is over Excel limit, so I am thinking of putting them in Power Pivot[/TD]
[/TR]
[TR]
[TD]But when I try to import the 4 regions, the existing data gets replaced so that I only have the last imported region … [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]How can I append and not replace the existing data ?[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]In advance, thank you for your help[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

use APPEND in PowerQuery (Get&Transform) then add to DataModel if necessary

 

[ExcelTeen]

Hi Sandy,

thanks for your answer. I am not so far, so I will have to dig !

 

[sandy666]

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]

[TABLE="width: 995"]
<tbody>[TR]
[TD]Hi Sandy,[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Yes, all files have the same structure.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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.[/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
[TR]
[TD]with no possibility to select … In fact, the two tables are not in the model but in the original file.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]What do I do wrong ?[/TD]
[/TR]
[TR]
[TD]Feel free to describe the relevant procedure as you would do it to a kid because I tried so many things without success ... [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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 :[/TD]
[/TR]
[TR]
[TD]Sources.xlsx contains the four regions (4 tables) I want to put together in the model[/TD]
[/TR]
[TR]
[TD]MyModel.xlsx contains the model (and unfortunately as described I don't know how to have a unique table with the different regions)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]In advance, thanks for your help[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

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:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Region[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Europe[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Europe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Europe[/td][/tr]
[/table]
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

 

[ExcelTeen]

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 !

 

[sandy666]

You are welcome

additionally:

• PowerQuery1
• PowerQuery2

you can use Thanks or/and Like button for appropriate post

 

[sandy666]

i missed french

• Commencer à utiliser Power pivot
• Mise en route de la transformation obtenir & dans Excel
• Aide de Microsoft Power Query pour Excel

 

[ExcelTeen]

Hi Sandy,

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