Month: February 2019

How to load Petro.ai into Excel

It’s taken me a while to fully consider the full implications of having a great API to query. Nearly every application has access to REST API calls and this makes for a really compelling way to access data. For all the years I’ve been using Spotfire, I haven’t given much consideration to Excel. But it’s an important application and it too, is improved through live data. In this blog, I want to show you how to get access to Petro.ai data through Excel!

Reference

First you’ll need the location of your Petro.ai instance from your administrator. For our demo database you can use http://tst-geologic.petro.ai/docs/index.html, provided by GeoLogic.

This is the reference to all the data that is available inside the tool. For our purposes, we just to pull out the well headers. That address is http://tst-geologic.petro.ai/api/Wells.

Alright on to step one where we setup the data source in Excel.

Setup the Data Source in Excel

You’ll need to use the data source functionality of Excel:

  1. From the Data tab, select Get Data > From Other Sources > From Web.
  2. Let add the web link to the dialog box, and we’ll limit it to 10 like below. Click Advanced and then look specifically at the URL parts.

  3. You’ll come up against a dialog like this, this is the Power Query Editor. We’re going to cheat and I’m going to give you the code to directly connect to it. First press Home.

  4. Click on Advanced Editor, take the code below and replace it with the text I put here:

let
    Source = Json.Document(Web.Contents("http://tst-geologic.petro.ai/api/Wells" & "?Limit=20")),
data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"wellId", "name", "operatorName", "entityType", "statusCurrent", "statusCurrentDate", "lastProductionDate", "permitDate", "spudDate", "completionDate", "leaseName", "leaseNumber", "wellNumber", "fieldName", "basinName", "lateralLength", "fracStages", "totalProppant", "totalFluidPumped", "perforationUpper", "totalDepth", "measuredDepth", "formationName", "stateName", "countyName", "countryName", "surfaceLoc", "midPointLoc", "bottomHoleLoc", "production", "prod", "eur", "eurLatest", "typeEUR", "typeEURLatest", "typeWeight", "groups", "id", "extra"}, {"petro.wellId", "petro.name", "petro.operatorName", "petro.entityType", "petro.statusCurrent", "petro.statusCurrentDate", "petro.lastProductionDate", "petro.permitDate", "petro.spudDate", "petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.wellNumber", "petro.fieldName", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.stateName", "petro.countyName", "petro.countryName", "petro.surfaceLoc", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "petro.prod", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "petro.id", "petro.extra"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.countyName", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "petro.prod", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "petro.extra"}),
    #"Expanded petro.surfaceLoc" = Table.ExpandRecordColumn(#"Removed Columns", "petro.surfaceLoc", {"latitude", "longitude", "type", "coordinates"}, {"petro.surfaceLoc.latitude", "petro.surfaceLoc.longitude", "petro.surfaceLoc.type", "petro.surfaceLoc.coordinates"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded petro.surfaceLoc",{"petro.surfaceLoc.coordinates"})
in
    #"Removed Columns1"

  1. Press Close & Apply, you’re finished!

Technical Director at Ruths.ai