Pivot Table from Related Tables

[dhofland]

Hi all,

I'm trying to display a bunch of data in a pivot table, but I'm having trouble getting it to show up like I want it to. My data is divided into multiple tables, but I believe setting up relationships between the tables could make it work. I have an example of the data (not my real data - just completely made-up information!) attached as a photo. Basically, all the data is related to a "name", and some of the data has only one entry per "name" (General Info table). Some of the data can have multiple entries per "name" (Repeated Info table), so I put that in a separate table so I don't have to duplicate all the other columns in the General Info table with each Repeated Info entry. I created a table relationship between the "Name" column of each table and tried to make a Pivot Table with the related tables. You can see that I put all the table columns as rows in the Pivot Table, arranged in tabular form. I anticipated that the Pivot Table would only show the "Jobs" linked to each "Name", but instead it shows all the "Jobs" for every "Name". I manually made what I wanted it to look like in the "What I Want" table. Does anybody know if it's possible to make the Pivot Table do what I want it to do? Should I arrange the data differently? Is it a Pivot Table setting? I would prefer that the "Repeated Info" table stay separate from the "General Info" table so that it's easy to add a line to the "Repeated Info" table if someone takes a new "Job".

Also, why doesn't the Pivot Table stay in Data Source Order when I select that option?

Thanks in advance,

Daniel

[#2 · Accepted solution]

@dhofland When creating a pivot table with fields from related tables, you need to have at least one field in the Values area. Drag the unused "Name" field from the "Repeated" table to the Values area to return only the related "Jobs" for each person...

Pivot Table with Related Records

Also, to answer your second question, if you're referring to the option to "Sort in data source order", this applies to the "Field List" shown in the PivotTable Fields Pane only...

Field List Options

[#3]

@djclements Thanks for that answer - that's what I was missing! I do have a couple of follow-up questions:

1. Is there a way to hide the "Count of Name" column from the Pivot Table, or would I have to hide all of column "N" to do that?

2. How could I add another table of repeated values to the Pivot Table and do the same thing? For example, could I create a table of character traits (charismatic, introvert, personable, etc...) with multiple traits per person (and create a relationship between the "Name" column of both tables) and add that to the pivot table the same way?

3. As for the data source order, I was referring to the filter dropdown from the "Name" column of the pivot table. I want the names to remain in the order listed in the "General Info" table.

[#4]

@dhofland Hiding column N would be easiest, but if you don't want to do that, you can also apply some custom formatting to the "Count of Name" column. Right-click on any value in the column and select Value Field Settings... Overwrite the "Custom Name" with a single space, then go to Number Format > Custom, enter three semi-colons (;;;) for the "Type" and click "OK" twice.

Value Field Settings > Number Format

This will essentially hide the values in column N of the pivot table. You can also hide the totals row, if desired. On the ribbon, go to PivotTable Design > Grand Totals > Off for Rows and Columns. The final result would look something like this:

Final Layout

Notice how the names have been rearranged in the desired order. This can be done manually by selecting a name, then moving your cursor to the bottom border of the selected cell until the mouse pointer changes to the "move" crosshairs, at which point you can just click and drag the name up or down to rearrange the order. I don't believe there is a way to do this automatically, other than by creating custom lists.

As to your inquiry about adding another one-to-many table relationship, it would be simpler to use Power Query to create the appropriate table joins and load the resulting query to a Pivot Table Report. Although, if your intention is to show all fields for all records together in one report, at least one of the fields will return duplicate values (due to the many-to-many relationship between the two tables with "many" records).

Pivot Table from Power Query

[#5]

@djclements Thanks for all your help on this. Looks like I have some decisions to make on whether I want to delve into Power Query.