Creating "Power Set" from two tables - possible in power pivot?

[ajm218]

Hi guys,

In table one I have a list of people and in table two a list of years I wish to do some analysis for. I really want to create a combination of the two to use (despite there being no link field). For example if I had:

Bob, Dave and Steve and wished to do analysis for them in 2007,2008 and 2009, can I create a table that looks like this:

Bob 2007
Bob 2008
Bob 2009
Dave 2007
Dave 2008
Dave 2009

etc?

In access this can be done by doing a query with multiple tables/queries and not defining any relationships.

Thanks for any help - just been introduced to power pivot today and getting my head around some of the ideas but this one has stumped me a bit.

Andrew

 

[Fazza]

Welcome, Andrew

I'm not familiar with power pivot - I'm on Excel 2003 - though assume it would be like Excel 2003: make a cartesian join of the tables.

So SQL like,

SELECT N.NameField, Y.YearField
FROM NamesTable N, YearsTable Y

Same as any database AFAIK.

Of if you had duplicate names or years in the source table just change to : SELECT DISTINCT etc, etc

HTH. regards, Fazza

 

[ajm218]

Thanks Fazza - so I need to do this in Access or similar before importing the table into Power Pivot?

 

[miguel.escobar]

You have 2 choices here:
1. You can use LinkBack Tables http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
2. You can create a pivot table with both of those fields in the rows and you should get a table with the combination of both tables.

You should check out CROSSJOIN() too

 

[Fazza]

Thanks Fazza - so I need to do this in Access or similar before importing the table into Power Pivot?

Andrew, I don't know about power pivot. I just assume it can handle a simple table join. In earlier Excel versions this can be done using the SQL I gave above. This does not need Access or similar: Excel can do that natively. hth

 

[miguel.escobar]

Andrew, I don't know about power pivot. I just assume it can handle a simple table join. In earlier Excel versions this can be done using the SQL I gave above. This does not need Access or similar: Excel can do that natively. hth

What Fazza says works but if you're not able to do this type of import and you're just stuck with tables inside Powerpivot then the options that I've given before would work just fine.