Creating a lookup table

[michaelh121]

Hi,

I've 5 tables with each containing a Site Code column that have a different number of site codes (including duplicate values).

One of the tables has all of the site codes unique so I tried using it as a lookup table.

I duplicated it and removed all the other columns leaving the one with unique site codes.

I then created many to one relationships with other tables.

When I try to visual some of the data using the site code from within the same table it shows me the correct output, but when I try to show the exactly same thing using a lookup table for site code, it ignores any duplicate values that I have.

What I want is to have a cross-table link that will allow me to show visuals using data from different tables.

Lookup site codesexample of site codes from other table

[konstantinos · Accepted solution]

@michaelh121 So you use 3 fact tables. 

Try to avoid two way relantionships.

You have multiple options depending the info your columns have in tables.

Best is to try data modelling in Query mode.

If you have all the site codes in one table you can right click the query/table and reference - > Select the Site codes column -> remove other columns -> Remove duplicates - Ready

* Keep in mind that Query treats capital & lower case letters as different while when loaded model will show an error as it treats as the same letter.

If the Site codes are scattered in all tables then repeat the proccess for each query and the merge append all and again remove duplicates.

[michaelh121]

Hi Matt,

My tables have multiple columns(about 20-30 per table), I can't show these columns beucase it is sensitive data.

That's the reason why I try to create a lookup is because I can't join these tables into one big table.

As I mentioned earlier, each table has the columns "Site Code" but 4 out of 5 table have duplicate entries in them and only one has unique values. What I want to do is to have a lookup that will reference back to those tables and pick all the site codes hence don't use the column "Site Code" from each table but just use the lookup table for it, I also hope that this will allow me to create cross-table visuals as I can't do this at the moment. 

[MattAllington]

This is a very strange looking data model. Take a read of this and see if it resonates. http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

From what i can see in the image, all these Columns would be fine in a single table. You only need to create lookup tables when your lookup tables (eg calendar) have a lot of columns for each key. From your image, each lookup table only has a single column