I am looking for some assistance in creating a distance table in Power Query that is dynamically generated for each combination of locations from my locations table.
My data model consists of the following table where every store and distribution centre is listed. A short sample is presented here. In reality my table holds 45.000 locations with their geographic longitude and latitude and receives a weekly update with new locations being added and others being removed.
From this table I would like to generate another table listing the distance between each and every location, with the condition that the distance is less than 3 kilometers. Given the 45.000 entries I reckon this will generate a table with max. 300.000 – 500.000 rows.
The resulting distance table should look something like this:
I managed to insert the proper formulas to calculate the distance between two geographic points (latitude, longitude) in Power Query. See attachment.
The main issue is how to generate a table holding all possible combinations of locations, with the condition that only combinations with a distance less than (e.g.) 3 kilometer should be kept.
I see how functions are used in Power Query to generate date tables, with a dynamic number of rows. Perhaps invoking a function is the correct way to generate this table?
Perhaps an approach is to have a function that generates a table with all possible combinations, calculate the distance and afterwards delete all rows with a distance > 3 KM. But it seems rather brute force to generate 45.000 x 45.000 = 2 billion rows in order to keep only some 400.000 of them.
Is it possible to have a function that already calculates the distance and based on the outcome creates a row for it or not? And how to update this distance table when de locations table gets a refresh?
Hopefully someone can help me out with this.
SebastiaanForum question M-code distance table.pbix (34.8 KB)