Forum Discussion

michaelh121's avatar
michaelh121
Frequent Visitor
10 years ago
Solved

Creating a lookup table

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 u...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    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.

     

     

     

MattAllington's avatar
MattAllington
Community Champion
10 years ago

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