Europe Underestimated: Why Power View Hates Bob Phillips, and a User Group Update

 
Lots more interest still flowing in through the registration page.  Updated status (changes in CAPS)…

Places that are ready:  NEW YORK, DALLAS, INDIANAPOLIS, KANSAS CITY, LONDON, NORTHERN ENGLAND, Portland, Detroit, Washington DC, Vancouver BC, Boston, Cincy/Dayton, Milwaukee/Madison, Seattle, Atlanta.

Places that are getting close:  CHARLOTTE, NASHVILLE, BIRMINGHAM (Alabama), GENEVA, COPEHNAGEN, OSLO, STOCKHOLM, Minneapolis/St. Paul,  Chicago, Buenos Aires, Cape Town, Perth, Melbourne, Sydney.

Places that just need an organizer:  NorCal and Denver still slacking in this regard Smile

But there’s an actual Power View technique/fix to be reported here, one that paints a much nicer picture of Europe especially…

image

Europe Has a Lot More Bubbles than Originally Thought Smile

I got a comment/question from Bob Phillips yesterday:

image

No, actually.  A missing bubble on the chart means Power View thinks you don’t exist.  (More fairly, Bing doesn’t think you exist, since Power View uses Bing to plot the bubbles.)  But I’ve talked to Bob in person.  I know he exists!  And I know there’s a city named Southampton, because Pink Floyd once did a song called Southampton Dock.  (See how cultured I am?  I’m no Ugly American, or am I…  see below.)  So there’s a problem here.  Power View – why you no like Bob Phillips?

Yesterday I also got this particularly pointed and creative response to the “What City/State/Country do you live in” question in the signup survey:

image

An Irishman Skewers the Ugly American Survey Writer
(Mental Note to Make Sure I Call it “State/Province” in the Future)

After some sleuthing, it turns out the two problems are related.

I had a calc column in the PowerPivot window (the survey results are exported from Polldaddy as CSV):

=[City] & “, ” & [State] & “, ” & [Country]

image

And I use that column as the location field for the map chart – those are the values that Power View feeds to Bing to find locations.

But some people, particularly in Europe, didn’t know what to enter for the State question, which I had made mandatory.

Sometimes they filled in the name of their City, resulting in “Copenhagen, Copenhagen, DK” as the Location field.  Bing “liked” that, and Power View therefore plotted a dot/bubble on the chart.

But if you entered the name of your Country there, resulting in something like “Herning, Denmark, DK” – Bing got confused.  And Power View doesn’t display any dot/bubble at all!  (Ditto if you entered “xxxx” or “not applicable.”)

So I changed the calc column to NOT use the State column unless Country=US:

=IF([Country]=”US”,
              [City] & “, ” & [State] & “, ” & [Country],
              [City]& “, ” & [Country]
   )

Giving me:

image

All of these Locations Now Show Up in Power View

Or at least, I think all of them now show up on the Power View map.  It would be nice if Power View would tell me which rows are not getting a result back from Bing in terms of location, so I could spot problems like this sooner and debug them more effectively once spotted.

Anyway Europe, you’re doing much better now Smile

11 Responses to Europe Underestimated: Why Power View Hates Bob Phillips, and a User Group Update

  1. wessexbob says:

    Well, I guess it is better to be hated and exist rather than not exist.

    I must admit that I thought I had entered Hampshire as state (after trying to ignore a mandatory field :(), but maybe not. I don’t actually live in Southampton, it is the nearest ‘commercial centre’. It is not the nicest city, so you won’t get many songs about the place. I maintain that I live in the ancient borough of Poole in the Kingdom of Wessex, I guess Bing would have had a field day with that.

    Anyway, glad to see we have a bubble, and great to see that Europe is better represented. It makes fascinating reading. France is a bit insular, just Paris .. come on France. And only one in Spain, not Madrid even (is that Burgos?). No Rome, only Czech Republic (can’t get used to calling it the Czech Republic rather than Czechoslavakia) in central Europe (Plzeno?).

    Does blue represent participants, brown for organisers?

  2. David Hager says:

    I believe that Nick Hodge still lives in Southampton.

    • Bob Phillips says:

      Nick lives in the country well outside of Southampton. But even though he is close, and even though he was always the keenest on every new shiny gizmo from MS, Nick isn’t very active in the community (all I ever see from him recently ar
      e pretty dull tweets about Southampton FC), and I don’t know if he uses or is even into PowerPivot.

      Ken Wright, a previous Excel MVP lives between Southamptoon and Pompey, and Richard Schollar lives Eastleigh way, but I know of no others.

  3. David Wettion says:

    Thanks for clarifying the mapping issue regarding city, state country. I was having the same problem and now I know how to handle the location data.

    One of the nic features in the new GeoFlow (an add-in for EXCEL 2013) is that it provides a “confidence” report on the mapping success rate. It gives confidence levels on what it’s mapped (on the very cool 3D maps). In this way you can see what is being mapped and what isn’t. I used this report to go back into the data and make adjustments to locations that weren’t being reported. I could then run PowerView reports on the corrected data knowing that the mapping would be fully inclusive.

    • powerpivotpro says:

      Great feature of GeoFlow. Didn’t know that yet. Perfect reason why MS needs to combine PV and GeoFlow.

      • Avichal says:

        Comparing this with Tableau (when I had last used it), Tableau put the points which cannot be geocoded at Lat, Long (0,0). Somewhat easier to spot and work with. But if something geocoded incorrectly (Paris, Texas instead of Paris, France or the other way around) that may be harder to detect.

        Geoflow does give a success rate, but if I understood it correctly it does not do a confidence level. So say, you could not filter out or choose to review the points geocoded with low confidence level. Example, ones which were only geocoded at Country instead of City level. In our BI team we are geocoding all our addresses using a geocoding service to store Latitude/Longitude. That would give us more flexibility in establishing confidence level, correcting mistakes etc. It’s still in progress and is indeed a bit of a pain. Hopefully the out of the box solution would keep improving in this regard.

  4. Avichal says:

    Rob, first of all thanks for kicking off the UserGroups effort. I am signed up already and looking forward to getting started. However as I was reading this post, I felt envious of Tableau, which has the Tableau Public version which lets you build and host Tableau Visualization on the web . Something similar would have been so cool for PowerPivot/Power View. In this case you could have shared live and interactive sheets of the world Power View maps.

    3rd party may be offering something like that (Pivot Stream?) but Tableau Public felt really easy to use. Would you consider dropping a word in the same regard when you speak to your Microsoft friends? :-)

  5. David Wetton says:

    Mapping has certainly increased my level of knowledge of world geography. It’s amazing where things get mapped as text names of locations are to be found in more than one place. It seems that the only 100% surefire way to do this is using lats/longs or some other (numeric) coding schema that prevents mapping to the wrong location. And with country names changing over time it creates challenges when trying to map data that spans such changes.

  6. kuntphong says:

    How can I get the correct name for bing to plot bubbles in powerview.

Leave a Comment or Question