Forum Discussion
Solved
Calculate nearest points by geographical coordinates
Hello! I have two tables. Table "Customers address" with my customers id, latitude and longitude (8 mln rows). And "Shop_table" with my shops id, latitude and longitude (500 rows). The goal i...
It seems, I figured how to do it
I made the user function
(reg,x_cast,y_cast)=>
let
Source = Table.SelectRows(shop,each [region]=reg),
formula = Table.AddColumn(Sorce, "Distance", each
6371 * 2 *Number.Asin(Number.Sqrt(Number.Power(Number.Sin((y_cast-[y_shop])*Number.PI/180/2),2)+Number.Cos(y_cast*Number.PI/180)*Number.Cos([y_shop]*Number.PI/180)*Number.Power(Number.Sin((x_cast-[x_shop])*Number.PI/180/2),2)))),
minimum = List.Min(formula[Distance]),
fnl_tbl= Table.SelectRows(formula,each [Custom]=minimum)
in
fnl_tbl
This might be useful to others, I've put togheter a sample PBIX that calculates the closest Shop to Costumers using Dmitry_D's original code. Sample on dropbox here.