Power Pivot - the basic question...

[Bilingual]

Hi, i have two files which has the same format but are to large to work with in a Excel file, Powerpivot should be my savior in this context.

But i can not create a relationship with the tables, because they contain Duplicate Values, is there a workaround when you have totaly similar data just based on two years?

 

[Matt Allington]

Best option is to use Power Query to load the first table, set it to "create connection only". Do the same with the second table. Then join both queries together (append) and load the data as 1 table to Power Pivot.

 

[Bilingual]

Best option is to use Power Query to load the first table, set it to "create connection only". Do the same with the second table. Then join both queries together (append) and load the data as 1 table to Power Pivot.

Thanks a lot, may i ask what are the disadvantages if i skip the Powerpivot leg and just use PowerQuery?

 

[Matt Allington]

You said they were too large to put in Excel, right? What will you do with the table if you can't put it in Excel and don't put it in Power Pivot?

 

[Bilingual]

You said they were too large to put in Excel, right? What will you do with the table if you can't put it in Excel and don't put it in Power Pivot?

You are totally right, i thought that PowerQuery also compressed the data, but no.

 

[Matt Allington]

Correct - it doesn't. However if you are wanting to use a flat file Pivot table, you can load directly from power query into the pivot cache and compress on load. Just select existing connections when creating a pivot table and select the query

 

[SpillerBD]

A user shouldn't need the "Power Pivot" add-in, but use the same engine by way of the "Data Model" options in a regular version of Excel.
Having the Power Pivot add-in available is a huge bonus, but not necessary.
So from PowerQuery, Load to Data Model is almost as good as Load to Power Pivot.