Select Page

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:

``=180/Number.PI``

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. 