Bing and PowerQuery Location Tool

 Write instructions to setup a Bing geocoder and reverse geocoder in Microsoft Power Query and provide a sample list of addresses and coordinates in a text file to use.

**Setting Up Bing Geocoder and Reverse Geocoder in Microsoft Power Query**

---

**Prerequisite:**

* Obtain a Bing Maps API Key from [https://www.bingmapsportal.com/](https://www.bingmapsportal.com/)

---

## A. Forward Geocoding (Address ➔ Coordinates)

### Step 1: Prepare Your Address List

* Create a text or CSV file with a header named `Address` and a list of addresses.

### Step 2: Load into Power Query

* Go to Excel or Power BI: `Data` → `Get Data` → `From File` → `From Text/CSV`

### Step 3: Add Geocoding Function

1. Open **Advanced Editor**.

2. Paste the following M code before the query:

```powerquery

let

    BingGeocode = (Address as text) =>

    let

        BingMapsKey = "YOUR_BING_MAPS_KEY_HERE",

        EncodedAddress = Uri.EscapeDataString(Address),

        URL = "https://dev.virtualearth.net/REST/v1/Locations?q=" & EncodedAddress & "&key=" & BingMapsKey,

        Source = Json.Document(Web.Contents(URL)),

        ResourceSets = Source[resourceSets],

        FirstSet = ResourceSets{0},

        Resources = FirstSet[resources],

        FirstResource = if List.Count(Resources) > 0 then Resources{0} else null,

        Coordinates = if FirstResource <> null then FirstResource[point][coordinates] else {null, null}

    in

        Coordinates

in

    BingGeocode

```

### Step 4: Use the Function

* Add a custom column: `BingGeocode([Address])`

* Expand the resulting column to extract `Latitude` and `Longitude`.

---

## B. Reverse Geocoding (Coordinates ➔ Address)

### Step 1: Prepare Coordinate List

* Create a file with `Latitude` and `Longitude` columns.

### Step 2: Load into Power Query

* Import via `Get Data` as before.

### Step 3: Add Reverse Geocoding Function

Paste the following in the **Advanced Editor**:

```powerquery

let

    BingReverseGeocode = (Latitude as number, Longitude as number) =>

    let

        BingMapsKey = "YOUR_BING_MAPS_KEY_HERE",

        URL = "https://dev.virtualearth.net/REST/v1/Locations/" & Number.ToText(Latitude) & "," & Number.ToText(Longitude) & "?key=" & BingMapsKey,

        Source = Json.Document(Web.Contents(URL)),

        ResourceSets = Source[resourceSets],

        FirstSet = ResourceSets{0},

        Resources = FirstSet[resources],

        FirstResource = if List.Count(Resources) > 0 then Resources{0} else null,

        Address = if FirstResource <> null then FirstResource[address][formattedAddress] else null

    in

        Address

in

    BingReverseGeocode

```

### Step 4: Use the Function

* Add a custom column: `BingReverseGeocode([Latitude], [Longitude])`

---

## Sample Data File: `Sample_Geocode_Data.txt`

```csv

# FORWARD GEOCODE TEST DATA

Address

1600 Amphitheatre Parkway, Mountain View, CA

1 Microsoft Way, Redmond, WA

350 Fifth Avenue, New York, NY

# REVERSE GEOCODE TEST DATA

Latitude,Longitude

37.4220,-122.0841

47.6396,-122.1280

40.7484,-73.9857

```

Save this as `.csv` and import it into Power Query to test your functions.