Improve data model - multiple relationship between tables

[P_D_G]

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

[CNENFRNL · Accepted solution]

@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.

[P_D_G]

I knew I was overcomplicated things... Thanks for the help!