So this challenge involved a lot of tricks that were great to practice with Power Query. It involved calculating distances, finding the closest branch for each csutomer, then making a priority list for each branch.
First, I set a constant for the conversion from decimal degrees to radians. That is as simple as creating a blank query with the following:
You could have done this in the calculation step itself, but sometimes if you reuse a constant like this it may be helpful to define it once and use it everywhere. That was not the case in this challenge, but I wanted to demonstrate that either way.
Next, I created a function to calculate distance after converting to radians, which was:
(lat1 as number, lat2 as number, long1 as number, long2 as number) => let Return = 3963 * Number.Acos( (Number.Sin(lat1) * Number.Sin(lat2)) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1) ) in Return
This gave you the pieces needed for calculations during the transformations. There were a lot of other steps, which involved (at least the way I solved it): a Cartesian join, pivotting, unpivotting, 2 nested table ranks/sorts, and probably some other trickery that I am forgetting as I type this.
Here is the video where I go over the steps, if you have any questions, reach out to me in the VizForVets Slack channel.