Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for the ‘Power Map’ Category

Web Services And POST Requests In Power Query

with 9 comments

In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.

For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:

http://msdn.microsoft.com/en-us/library/jj158961.aspx

As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:

let

    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",

    //Latitude

    Latitude = 53,

    //Generate a list of longitudes

    Longitudes = List.Numbers(-4.5,66,0.1),

    //Generate a list of latitudes and longitudes

    PointList = List.Transform(Longitudes, 

        each  Number.ToText(Latitude) & "," & Number.ToText(_)),

    //Turn this list to comma delimited text

    PointListText = Text.Combine(PointList,","),

    //Add the text "points=" to the beginning

    PostContents = "points=" & PointListText,

    //Call the Elevations web service

    GetElevations = Web.Contents(

                    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

                    [Content=Text.ToBinary(PostContents)]),

    //Treat the result as a JSON document

    ImportedJSON = Json.Document(GetElevations),

    //Navigate to the elevations data

    resourceSets = ImportedJSON[resourceSets],

    resourceSets1 = resourceSets{0},

    resources = resourceSets1[resources],

    resources1 = resources{0},

    elevations = resources1[elevations],

    //Turn the elevations data into a table

    TableFromList = Table.FromList(elevations, Splitter.SplitByNothing(), 

                                null, null, ExtraValues.Error),

    //Rename the column containing the elevations

    RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Elevations"}}),

    //Add a column containing the latitude

    InsertedCustom = Table.AddColumn(RenamedColumns, "Latitude", each Latitude),

    //Add an index column

    InsertedIndex = Table.AddIndexColumn(InsertedCustom,"Index"),

    //Use the index column to find the longitude for the current row

    InsertedCustom1 = Table.AddColumn(InsertedIndex, "Longitude", each Longitudes{[Index]}),

    //Remove the index column

    RemovedColumns = Table.RemoveColumns(InsertedCustom1,{"Index"}),

    //Set all columns to data type number

    ChangedType = Table.TransformColumnTypes(RemovedColumns,

                    {{"Elevations", type number}, {"Latitude", type number}, 

                    {"Longitude", type number}})

in

    ChangedType

 

It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…

Two things to point out:

  • To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at https://www.bingmapsportal.com
  • The important step is GetElevations. The code is:

    Web.Contents(

    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

    [Content=Text.ToBinary(PostContents)]),

    You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.

Here’s the output in Power Map:

image

You can download the sample workbook here.

Written by Chris Webb

April 19, 2014 at 9:00 am

Pretty Patterns With Power Query And Power Map

with one comment

Here’s something with no practical use whatsoever. Today, after I finished writing the first draft of the chapter on M of my upcoming Power Query book, I got thinking about how Power View and Power Map get all the attention because of all the eye-catching demos you can create with them. And then I thought – why bother spending time finding real data for these demos when you can generate artificial data in Power Query to create patterns? So I got to work…

As you probably know, you can create animated charts in Power Map so long as you have date-based data. I therefore created a function in Power Query to draw a circle as a series of points on a graph where each point is associated with a date; I also added data for height and colour for each point. Here’s the function definition:

let

    //declare function to draw a circle

    CircleFunction = (CircleRadius as number, StartDate as date, Reverse as logical) =>

let

    //set the radius

    radius = CircleRadius,

    //create a list of numbers from 0 to 359

    anglelist = List.Numbers(0, 359, 1),

    //function to convert degrees to radians

    radians = (a) => (a * 2 * Number.PI)/360,

    //create a list of 360 dates starting from the start date

    unordereddatelist = List.Dates(StartDate, 360,#duration(1,0,0,0)),

    //reverse the list of dates if the Reverse parameter is True

    datelist = if Reverse then List.Reverse(unordereddatelist) else unordereddatelist,

    //generate the list of points on the graph, one for each angle and date

    positionlist = List.Transform(anglelist, each 

                    {_, datelist{_}, Number.Cos(radians(_)) * radius, 

                    Number.Sin(radians(_)) * radius, Date.Month(datelist{_}), 

                    Number.Abs(Number.Cos(radians(_)))*10}),

    //convert the list of points to a table

    outputtable = Table.FromRows(positionlist, {"Angle", "Date", "x", "y", "Colour", "Size"}),

    //set data types

    ChangedType = Table.TransformColumnTypes(outputtable,

                    {{"Angle", type number}, {"Date", type date}, {"x", type number}, 

                     {"y", type number}, {"Colour", type number}, {"Size", type number}})

in

    ChangedType

in

    CircleFunction

 

I then created another Power Query query to call this function 30 times to create 30 circles with different radiuses:

let

    //generate a list of numbers from 0 to 29

    circlelist = {0..29},

    //generate a list of 30 dates starting on 1 January 2014

    datelist = List.Dates(#date(2014,1,1), 30,#duration(1,0,0,0)),

    //call the Circle() function 30 times

    tablelist = List.Transform(circlelist, each Circle(_+5, datelist{_}, Number.Mod(_,2)=0)),

    //combine the resulting tables into a single table

    positionlist = Table.Combine(tablelist)

in

    positionlist

 

And here’s the result of the query plotted on a map using Power Map:

Pretty, isn’t it? You can download the workbook with the Power Query query and the Power Map tour here.

Written by Chris Webb

March 20, 2014 at 11:34 pm

Posted in Power Map, Power Query

Power Map Is Released

with 7 comments

Power Map was released as part of Office 2013 SP1. You can read the announcements here:

http://blogs.msdn.com/b/powerbi/archive/2014/02/25/power-map-for-excel-now-generally-available-automatically-updated-for-office-365.aspx

http://blogs.technet.com/b/office_sustained_engineering/archive/2014/02/25/announcing-the-release-of-service-pack-1-for-office-2013-and-sharepoint-2013.aspx?WT.mc_id=blog_PBI_GA_PowerMap

One important point to note here is that Power Map will only be available to Office 365 customers. If you have a standalone version of Excel, or have a regular (ie not Office 365) Professional Plus license, you will no longer be able to use Power Map. See:

http://office.microsoft.com/en-us/excel-help/power-map-for-excel-HA104204034.aspx?redir=0

I quote:

If you have a subscription for Microsoft Office 365 ProPlus, Office 365 Midsize Business, or for the Office 365 Enterprise E3 or E4 plans, you’ll have access to Power Map as part of the self-service business intelligence tools. To determine which subscription you have, see Office 365 ProPlus and Compare All Office 365 for Business Plans.

If you have Office 2013 Professional Plus or a standalone version of Excel 2013, you’ll be able to download and use the Power Map Preview for Excel 2013 until May 30, 2014. After that date, the preview will no longer work in any non-Office 365 subscription version of Excel.

So, yet more evidence that you need an Office 365 subscription and a streamed installation of Office to get all the latest BI functionality.

UPDATE: Meagan Longoria has the details on what’s new in this release here:

http://datasavvy.wordpress.com/2014/02/25/power-map-for-excel-is-now-generally-available-for-office-365-with-a-few-new-features-and-bug-fixes/

 

 

Written by Chris Webb

February 25, 2014 at 9:24 pm

Posted in Power Map

New Version Of Power Map Available

with 7 comments

Last week a new, preview version of Power Query was released to work with the Power BI public preview (John White, whose blog has a lot of good Power BI information, has the details here); today, a new version of Power Map was released too. You can download it here:

http://www.microsoft.com/en-us/download/details.aspx?id=38395

Here are some of the new features, in no particular order:

  • You can now overlay certain geographical regions onto a map. For example, with the following table in the Excel Data Model:
    image

    In Power Map you can create a new layer type of Region and see each country shaded by their sales value:

    image

    The Region shapes are sourced from Bing; you can’t upload your own shapes unfortunately. I have no idea what regions Bing does know about, but it clearly knows about English county boundaries (though it doesn’t know about postcode boundaries):

    image

  • You now have the option of seeing a ‘Flat Map’. Here’s the map above shown in flattened form:

    image

  • You can now control the colours used in a layer:

    image

  • You can add annotations with images in:

    image

    image

  • You can now record tours from within Power Map and save them to an MP4 file.
  • Calculated columns and hidden columns can now be referenced in a layer.

There are plenty of other changes – I’ll update this post if I’ve missed any other major ones – but in addition the app seems smoother and faster, as well as being (slightly) easier to use.

Written by Chris Webb

September 11, 2013 at 10:48 pm

Posted in Power BI, Power Map

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers