Consolidate Multiple Ranges

[GorD]

I'm keen to try and learn a bit about powerpivot but not much experience so far - other than a bit of reading.

I'm wondering if it can be easily used to "consolidate multipe pivot ranges" ( in old speak)


I have some data coming from 2 related BW queries that really needs to be merged to get the full picture( timesheet/hrs booked data). Maybe 5 out of 10 fields match - name, contract,dept, hrs - for example, across the two tables.

Is this achievable in Power pivot

At present as i don't like the consoildate multiple ranges approach, I do two separate pivot tables and then a 3rd table 2 merge them using sumifs.

Any pointers appreciated

 

[Jacob Barnett]

Definitely sounds like PowerPivot is a good solution to your problem. There are a couple of things to note:

- You will need to create a relationship between the two tables after import and this can only be done on a single column. To deal with this you will need to create a unique key in each table by concatenating all/some of the 5 fields together.

- To make the relationship directly one of the 'relationship' columns needs to contain unique values. If this isn't the case then you will need to create a 3rd table containing unique 'keys'. This is actually good model design as it potentially allows you to remove a bunch of duplication across the two tables.

 

[GorD]

I'm not sure i explained that very well, so could you clarify if the above still holds with the following,

The records in the second table are really additional records, not additional fields that are related to one of the other records

So table one may have 50k records for one group of employees, table 2 may contain 20k records - i really want the pivot table to have 70k records, and to further complicate ( although perhaps i can fix this) not all the fields are the same - table 1 may have 15 fields and table 2 may have 10 - but at least 5 and the ones i want to use in the pivot table contain the same type of data

Many thanks

 

[Jacob Barnett]

OK that clarifies things! PowerPivot is definitely central to the way I would attack this.

Option 1
There is a technique detailed here: PowerPivot from Identical Structure Excel Files that explains how to query Excel files with SQL and uses the 'UNION' function to turn 2 identical tables into one table. For UNION to work the 2 (or more) data sources have to be of the same structure i.e. same columns and datatype per column however this doesn't mean that the whole tables have to be identical, just the part you are selecting. Don't know if you have any SQL experience but writing a couple of simple SELECT queries is easy.

Option 2
Is to use Download Data Explorer for Excel - Office.com to create a single Excel table from the 2 files that you link back into the PowerPivot model. I haven't used this yet but from what I read I am 99.9% sure it is capable of performing this task (and MUCH more).

Option 3
Is to manually (or even using the SQL approach in option 1) create a unique table of keys that you can relate each of your fact tables to. Your measures would then add the relevant columns in each of the 2 tables. e.g. [Hours Worked] = SUM(table1[Hours]) + SUM(table2[Hours]).

 

[GorD]

Many thanks for your help - certainly some food for thought there - i'll do some reading

Thanks again

 

[GorD]

duplicate