2023 Preppin Data Week 11 – With Power Query!

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.

Bitnami