Using the Excel Data Model to Combine Multiple Tables into a Single Pivot Table

[@user-lw1pb8fs4r]

Although the way you set up the relationship works for this example, for a more complex scenario this is not the way you should set your relationships. The "Table" field should take the "Many" sides of a relationship. This is where usually the facts tables goes into. In your particular case that will be the Sales table (for each row in the Office table you will get many rows on the Sales table). On the Related Table you should put the "One" side of the relationship. In your case that would be the Offices table. This is why those column fields are labelled as "Foreign" and "Primary". On the "Related table" you choose the column of that table that acts as the Primary key, meaning it has unique values that are not repeated on that table. For the "Table" you choose the column that acts as a "Foreign " key on the relationship, meaning "the column that I use here with values that may be repeated multiple times and that is the primary key somewhere else - here it's just used as a bridge to the other table). So, even if the data model is simple, putting the right tables on the right field is always a good practice.

[@ahmadelshanwany720]

Can consolidated table be generated into one REGULAR table not pivot table?

[@ubaidillahmuhammad20]

put the excel files in the description video..