Forum Discussion
Improve data model - multiple relationship between tables
Hi all,
I'd like to recreate my data model in a more efficient way.
I have a Table1 with transaction data, this data is also geocoded an contains only unique rows. However, I also have Table2 which contains the nearby locations related to Table1.
Table1
| TransactionYear | Location | Lat | Long | Value_1 | Value_2 |
| 2019 | Location_1 | 19.03336 | 29.6116 | 21 | 193 |
| 2019 | Location_2 | -14.258 | 42.95751 | 192 | 99 |
| 2019 | Location_3 | -66.9449 | -38.4787 | 118 | 15 |
| 2019 | Location_4 | -59.3971 | -60.5873 | 110 | 53 |
| 2019 | Location_5 | -60.0055 | -19.878 | 40 | 82 |
| 2019 | Location_6 | -35.9316 | -118.366 | 108 | 155 |
| 2019 | Location_7 | 66.3981 | -52.6308 | 44 | 1 |
| 2019 | Location_8 | 39.34878 | -112.259 | 128 | 170 |
| 2019 | Location_9 | 57.37424 | -151.717 | 50 | 197 |
| 2019 | Location_10 | 17.8441 | -60.5622 | 144 | 161 |
Table2
| Location | Related_Location |
| Location_1 | Location_2 |
| Location_1 | Location_3 |
| Location_1 | Location_4 |
| Location_10 | Location_1 |
| Location_10 | Location_3 |
| Location_10 | Location_6 |
| Location_2 | Location_1 |
| Location_2 | Location_10 |
| Location_2 | Location_5 |
| Location_3 | Location_4 |
| Location_3 | Location_7 |
| Location_3 | Location_8 |
Every time I select one record from the table on the left I'd like to display the selected location (middle map) and all the nearby locations (right hand map).
Displying the selected location on the middle map is easy, however I need the location of the nearby locations too. Currently I'm using lookupvalue (I could use merge in Power Query too) to get the lat-long value pairs from the first table, but I think that this is creating a lot of redundancy as I already have this info in Table1. All in all I believe that this may not be the best way, but I couldn't come up with a better way to create this when playing around with different "fact" tables or relationships.
Any idea how to improve the model?
Example file here.
Thanks,
PDG
P_D_G , a simple but tricky solution to your issue is to append a "self-referenced" table to Closest_Locations table in PQ,
In the meantime, retrieve lat/long info from Main_Table by simply merging them in PQ; then when you choose one location from Main_Table, the chosen location, along with all related locations show on the map simultaneously.
2 Replies
P_D_G , a simple but tricky solution to your issue is to append a "self-referenced" table to Closest_Locations table in PQ,
In the meantime, retrieve lat/long info from Main_Table by simply merging them in PQ; then when you choose one location from Main_Table, the chosen location, along with all related locations show on the map simultaneously.
I knew I was overcomplicated things... Thanks for the help!
