The WorkerThread Blog

we know stuff so you don't have to

Excel 2013 Preview – Webservice Function and Flash Fill

Posted by workerthread on August 9, 2012

From everything I’ve seen so far Excel 2013 has some great new features for anyone interested in data analysis and Business Intelligence tools.   Both PowerPivot and Power View are included in this release (more on this later) and there are lots of new OLAP-specific features for anyone using Excel as a front end for multi-dimensional tools.

I saw this blog post from Chris Webb with an example of the new WEBSERVICE function.   As it happened I was recently looking at a file of locations for London bicycle hire stations – I thought it might be interesting to use this with some bike journey statistics against Excel 2013 Power View and maybe use the new mapping feature to show the busiest hire locations.   My file showed geographical location info as Easting and Northing and I thought I would be a good exercise to see if I could convert these to latitude/longitude columns for use in a Power View map.

Hunting around online, I found this article on converting easting/northing to latitude/longitude for an interactive map project.  The article uses a web service at uk-postcodes.com to send an easting/northing pair and return the lat/long data in JSON format.   So will this work with Excel 2013 –

Excel2013WebService

The answer is, yes it will, and I get JSON data returned which looks like this –

{"lat":"51.523344763137","lng":" -0.1838464075195"}

And of course I can copy the formula containing the WEBSERVICE function down the column -

Excel2013JSON

Next I need to parse the JSON data so I end up with two new columns, one each for Latitude and Longitude.   Now it is of course possible to split strings of single-column data into separate columns using various combinations of MID, FIND and LEN functions and this would certainly work.  And there are various Excel VBA JSON parser examples around online.  But Excel 2013 has a new, interesting Flash Fill feature which you can read more about in this Excel Team blog post.   Giving Excel 2013 sample patterns to work with is the key here.  I wanted to try this with the data in my web service results column.

It’s worth noting a point made in step 3 of the Excel team post: “We decided to disable automatic Flash Fill for numeric data. After all, there are only 10 unique characters in our numbering system, and it’s just too easy to come up with random patterns that don’t make good sense”.   So to pull out the latitude values as numerical values I needed to to format the numerical precision on the column (in this case to 12 decimal points) and use the Data tab “Flash Fill” option –

And it worked!

Excel2013Lat

My longitude values didn’t flash fill with numbers so successfully.   I think the problem here lay with the variable number of decimal places, so I got this message -

Excel2013precision

But the good news is if I entered the first example value with a label prefix, as soon as I start typing the second value, Flash fill recognises what I’m doing and suggests the fill I’m looking for -

Excel2013lng

UPDATE – if I settle for a lower precision and go for lowest common denominator of decimal places this does actually work with numbers.  So for example entering first 2 values with 8 digits of decimal value (such as –0.1838464) will allow me to flash fill with numeric data all the way down the column with no errors.

So overall pretty encouraging results.   Moving forward, if I were going to use the cleaned up web service results mashed up with other data in PowerPivot/Power View I would probably remove the web service function calls leaving behind just the cleaned-up data, thereby minimising the workbook refresh time.

One slightly disappointing finding, the WEBSERVICE function doesn’t currently seem to work in either SkyDrive or Office Web Apps in the Office 365 preview but maybe this will change by the time Excel 2013 is released.

Next up, a post on Excel 2013 PowerPivot and Power View.

About these ads

2 Responses to “Excel 2013 Preview – Webservice Function and Flash Fill”

  1. Chris Webb said

    Cool – I didn’t manage to get the WebService() function to work with a JSON response, so well done!

Sorry, the comment form is closed at this time.

 
%d bloggers like this: