Using data model and power pivots to link tables in Excel

[GeorgeCarter]

I have three tabs, each with a simple one column table, named respectively, Models, Options, Components. The rows in each table consist of the unique names of the product models, options and components.


I have another two tabs, each with a table with two columns that maps various options to each model, and various components to each option respectively.


I have added all five tables to the data model and created relationships between the columns with common names (ie model in the Models table and model in the Options to Models mapping table etc).



I would like to be able to create a pivot table that lists all the models, along with the options that are mapped to that model, and the components that are mapped to those options. Ultimately, it might look something like this:



I have tried creating a pivot table from the data model, but when I add the models, options and components fields to Rows, it shows ALL options for each model and ALL components for each option, rather than just the mapped ones.

Can anyone please tell me where I am going wrong?

Thanks.

 

[jimihen1]

I have the same problem with Power Pivot, in general. Any time I establish a relationship between two tables and then create a pivot table, any reports that leverage that relationship return values based on ALL entries instead of limiting the query to the matching values across the related fields.

For example, I have a table that contains various attributes of countries with repeating country names. I created a relationship with a country table that contains demographics of each country. The relationship is one-to-many from the country table (country name) to the attributes table (country name), as expected. But when I try to create a pivot table with the name of the country from table1 along with the population from table2, I instead get the same exact population for every country, which is the sum of all of the populations of all the countries.

Please help!

 

[RoryA]

That is not the same problem as the OP here at all (which was simply down to there not being a value field in the pivot table, as far as I can see). Your issue is that your relationships are the wrong way round for what you are doing. If you need them the way you have them, then you will need to write measures in DAX to return the values you need, rather than relying on the implicit sum measures that are available by default.

 

[jdellasala]

Unless I'm missing something (which would NOT be unusual!), to me it looks like you only need the three pretty color tables into Power Query and then do this:

let
    Source = Excel.CurrentWorkbook(){[Name="Models"]}[Content],
    AddedOptions = Table.AddColumn(Source, "Options", each Options),
    ExpandedOptions = Table.ExpandTableColumn(AddedOptions, "Options", {"Options"}, {"Options"}),
    AddedComponents = Table.AddColumn(ExpandedOptions, "Components", each Components),
    ExpandedComponents = Table.ExpandTableColumn(AddedComponents, "Components", {"Components"}, {"Components"}),
    ChangedType = Table.TransformColumnTypes(ExpandedComponents,{{"Models", type text}, {"Options", type text}, {"Components", type text}})
in
    ChangedType

That results in a table that looks like this:

Book1
	G	H	I
1	Models	Options	Components
2	Model A	Option 1	Component 1
3	Model A	Option 1	Component 2
4	Model A	Option 1	Component 3
5	Model A	Option 1	Component 4
6	Model A	Option 1	Component 5
7	Model A	Option 1	Component 6
8	Model A	Option 1	Component 7
9	Model A	Option 1	Component 8
10	Model A	Option 1	Component 9
11	Model A	Option 1	Component 10
12	Model A	Option 2	Component 1
13	Model A	Option 2	Component 2
14	Model A	Option 2	Component 3
15	Model A	Option 2	Component 4
16	Model A	Option 2	Component 5
17	Model A	Option 2	Component 6
18	Model A	Option 2	Component 7
19	Model A	Option 2	Component 8
20	Model A	Option 2	Component 9
21	Model A	Option 2	Component 10
Sheet1

which isn't needed but I posted it to give you an idea of what's in the final table. It can just be loaded to a Pivot Table that looks like this:

Book1
	O	P	Q
6	Models	Options	Components
7	Model A	Option 1	Component 1
8			Component 10
9			Component 2
10			Component 3
11			Component 4
12			Component 5
13			Component 6
14			Component 7
15			Component 8
16			Component 9
17		Option 1 Total
18		Option 10	Component 1
19			Component 10
20			Component 2
21			Component 3
22			Component 4
23			Component 5
24			Component 6
25			Component 7
26			Component 8
27			Component 9
28		Option 10 Total
29		Option 2	Component 1
30			Component 10
31			Component 2
Sheet1

That is copied from the actual pivot table - XL2BB won't copy a Pivot Table!.
BTW, the pics are very pretty, especially around Easter time, but XL2BB would be more helpful - even if I'm completely off base here!