I would like some help in improving my query. In the attached PBIX I created a query where two tables are cross joined, a distance calculation is being performed on the resulting table and rows are being filtered for a specific distance threshold.
The query works fine technically, but lacks proper performance, even when using Table.Buffer.
Both input tables consist of 7.000 rows and 13.000 rows respectively. This results in a cross joined table of 7.000 x 13.000 = 91 million rows of which after filtering for the threshold only 150.000 remain.
Therefore I would like to integrate the distance threshold as a condition in the actual merge operation, which should result in less rows being generated in the merged table.
Can someone help me with adjusting the query in this way?
There is no need to integrate the exact distance calculation in the merge condition. A more simple condition like: “not cross joining rows where both Lattitude and Longitude differ more than 0.1” would be sufficient.
Addresses FROM - TO.xlsx (14.3 KB)
Calculating distances.pbix (106.7 KB)