…or, if you want the clickbait title of this post, “Creating cool Minecraft-type landscapes in Power Map”…

I’m a big fan of Owen Boswarva’s UK open data blog, and yesterday this post on English LiDAR data released by the Environment Agency caught my eye:

http://mapgubbins.tumblr.com/post/131424021480/open-data-release-of-aerial-lidar-data-for

I won’t repeat what he says because there’s nothing I could add to it, but basically this data is a 3D scan of large parts of England at extremely detailed resolutions. You can download the data from this site:

http://environment.data.gov.uk/ds/survey#/download

My first thought after reading this was post was that it should be possible to load this data into Excel (in general) and Power Map (in particular) using Power Query fairly easily. And indeed it was – so in this blog post I’ll show you how.

First of all, here’s a sample of the output. This is a Power Map custom map showing tile SU9090, which shows a section of the M40 motorway and its surroundings near High Wycombe:

Zooming in on an area just below the centre of the above screenshot you can see several distinctively-shaped office buildings, the M40 going across the screen and, on the top right-hand side, a Tesco superstore:

Here’s the same area shown in Bing Maps (http://binged.it/1RkakM7):

Here’s the obligatory video:

How did I do this? The first step is to load the data into the Excel Data Model using Power Query. I created a Power Query function called LoadLiDARData to do this, and you can get a sample workbook with it in from here. There isn’t much to say about the function – it’s not trivial, but not very interesting in itself. To use it, in Excel go to the Power Query tab on the ribbon and open the Workbook Queries pane. To load data from a single .asc file downloaded from the Environment Agency site, right-click to invoke the function and enter the filename (including the full path) of that file. The Power Query Editor window will open but you don’t need to do anything here except click the Close & Load button. I recommend you load the output to the Excel Data Model rather than a table in the worksheet because a single file contains a lot of data!

Next, go to the Insert tab on the Excel ribbon and click the Map button to open Power Map. Then click New Scene\New Custom Map to create a new custom map. You need to make sure that the Min values for the X and Y axis are set to 0, the Max value for X is set to the number of columns and the Max value for Y is set to the number of rows in the data you’re working with. Also, check the Flip Axis button for the Y axis.

Then, on the right-hand side of the screen check the ColumnNumber and RowNumber boxes and set them to be the X Coordinate and Y Coordinate respectively:

Click the Next button, then check Height to use as the height of a bar graph:

Finally – and this is important – go to Settings and adjust the Thickness property appropriately. If you don’t do this you won’t be able to pick out any detail; I have used 18% as a good starting setting. Ticking the Lock current scale box is also a good idea, so that when you zoom in the bars don’t disappear.

This is no more than a proof-of-concept. I’ve only tested my code with a very limited amount of data, and there are a lot of obvious improvements that could be made: for a start, it would be good to convert the grid co-ordinates used to latitude and longitude so that this data can be used with the maps in Power Map. I could also imagine some really useful applications for this, such as combining a map like this with snowfall data and creating an animation showing where snow falls over a landscape during the winter.

Of course a dedicated tool will do a much better job of visualising this data than Excel, but if you have the right edition of Excel 2013 then you can get everything you need to do this for free and in Excel 2016 Power Query and Power Map (as Get & Transform and 3D Maps) are available out-of-the-box. It also sounds like Power Map will be integrated with Power BI at some point too:

UPDATE: Here’s a video of another tour I created using LiDAR data, showing Waterloo Station, the Houses of Parliament and Westminster Abbey in central London

Recently, I was working with a customer that wanted to be able to draw lines between two points in Power Map. At the time I thought the only way that it might be possible was by using Power Query to generate a table of data containing a series of points that were so close together that they looked like a single line, and then plot these points in Power Map (similar to what I show in the screenshot here). Soon after, the new custom regions functionality was released in Power Map (there’s no documentation I can find right now, but this blog post is reasonably detailed) and I wondered whether now it might be possible to draw lines. Unfortunately not: Power Map can now import SHP and KML files, but it doesn’t support all the features of KML – only polygons (and even then not all the features of polygons, although inner and outer boundaries work fine). I guess this is ok for the primary use-case of Power Map, which is plotting BI data on a map, but it would be nice to see more KML features supported so that Power Map can show richer supporting information for the data: things like arrows showing direction of travel, and so on.

Anyway, I then thought – why not use polygons to draw these lines? Again, I hit a slight problem: I wanted to generate the polygons for the lines in code, and Power Map can only import SHP or KML data from files. It would be really useful if we could use shape data stored in the Excel Data Model… but we can’t. However, it is possible to use Power Query to generate KML and then copy and paste this code into a file, which can then be imported into Power Map. So, just for the fun of it, I put together a proof-of-concept workbook containing Power Query queries to generate all the tables and KML code needed to draw lines between two places, and a Power Map tour that shows the output. Here’s what the end result looks like:

You can download my example workbook that contains all the code, plus all the supporting files, here. You will need to update some file paths in the M code to get it all to work.

The starting point is two tables on the worksheet, one containing the single starting point for the lines, the other all of the destinations:

There’s a lot of M code so I’m not going to include it in this post, but here’s an overview of what each query does:

• GetLatLong is a function that calls the Bing Maps REST API to find the latitude and longitude for each place in the tables above. You will need your own Bing Maps account key if you want to use this code yourself – you can get one at https://www.bingmapsportal.com/
• Starting Point and Ending Points simply load the data from the Excel tables
• StartingPointLatLong gets the latitude and longitude of the starting point by calling GetLatLong
• StartEndPoints gets the latitude and longitude of all the ending points by calling GetLatLong, adds custom columns to show the starting point name, latitude and longitude against each ending point, and loads the result to the Excel Data Model. You have to have some data in the Excel Data Model for Power Map to display the lines, and it’s important that Power Map can match the values in one column in this table with the names of objects in the KML file.
• BasicPolygonTemplate loads a fragment of KML, containing the definition of a polygon, from a text file. This contains two ‘parameters’, @Name and @Coordinates, which will be overwritten using Text.Replace() later on when the actual KML is being generated.
• GetCoordinateList is a function to draw a rectangular polygon that represents the line between the starting point and an ending point. I had a lot of fun trying to get the code for this working properly (I wish I could remember any of the trigonometry that I learned after the age of 13…) and I’m still not convinced the rectangles are properly rectangular, but they’re good enough.
• KML generates the KML for all of the polygons. The output of this query must be copied from the Power Query query window into a text file with the .kml extension, for example Test.kml. There’s no need to load the output of this query to anywhere.

With all of that done, you now need to open Power Map and create a new tour. Choose EndingPoint as the sole Geography column, then choose Custom Region (.kml, .shp) from the dropdown list below and click Yes to import custom regions.

Select the .kml file you created earlier, containing the output of the KML Power Query query, and then click Import:

Finally, change the visualisation type to Region and optionally add Ending Point to Category to make the lines different colours:

And bingo, you see the lines:

Support for custom regions is a massive step forward for Power Map in my opinion: rather than just being a toy for creating flashy demos it’s now able to handle a lot more real-world requirements. However, having some way of programmatically creating regions and shapes (either through Power Query as I’ve done here, or using VBA or some other API), being able to load shape data from the Excel Data Model, or even just to be able to draw shapes on a map manually, would be welcome. I’m no mapping expert but I’ve come across a few frustrated Mappoint (which was discontinued at the end of 2014) users who would like to use Power Map but find that it can’t do everything that they need. The code in this post shows what’s possible but it’s still way too complex for most users and hardly an elegant solution.

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.