Rendering Text On A Map With Power Query And Power Map

They say a good magician never reveals his tricks. Luckily, I’m a rubbish magician and so (at the request of Jason Thomas and David Eldersveld) I can reveal the secret behind one of the magic tricks I used in my sessions at SQLBits and the PASS Summit last year: my mind-reading card trick. More specifically, this post will show you how I was able to find out which card my helper had picked from a deck and how I was able to make its name appear in Power Map. Alas the video of the hour-long SQLBits session isn’t available for some reason, but the video of the shortened version of the session I did at the BI Power Hour at the PASS Summit is available for purchase here along with other, much more useful content.

First of all, the mind-reading bit. I hate to admit it but it had nothing to do with this rather fetching hat – the Microsoft Brain Interface – modelled here by Matt Masson:


In fact I had some accomplices (Bob Duffy and Carmel Gunn at SQLBits, Matt Masson at PASS) who entered the name of the card into a text file stored in OneDrive when my helper showed it to the audience. I then used the technique Melissa Coates blogged about here to read the name from the text file using Power Query on my laptop.

Second, the harder problem of taking the name of the card and making it appear in Power Map. Again, I used the magic of Power Query. On an Excel worksheet I used 1s and blanks to represent each letter of the alphabet in its own 8×8 grid:


…and then I read this into Power Query, matched each 8×8 grid up to the letter it represented, split the name of the card into letters, put the two together and got a table containing a set of data points that, when plotted on a custom map in Power Map, could be read as text:


You can download a modified version of the workbook here which reads data from the worksheet instead of a text file and renders the text over a regular map in Power Map. Here’s an example of the output:

Sadly I’m even less of an artist than I am a magician, and I admit that the output could be prettier. David Eldersveld recently showed off a very impressive Power BI report that uses a scatter chart to display the Power BI logo (you can see the report live here):


I guess the logical next step would be to take my code, move it to Power BI, add a column with date values in and use the play axis on the scatter chart to display animated text or pictures. But, to be honest, that might be too geeky a project even for me.

Loading English LiDAR Open Data Into Excel And Power Map Using Power Query

…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:

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:

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 (


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

Drawing Lines On Maps With Power Map And Power Query

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
  • 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.

What’s New In The Excel 2016 Preview For BI?

Following on from my recent post on Power BI and Excel 2016 news, here are some more details about the new BI-related features in the Excel 2016 Preview. Remember that more BI-related features may appear before the release of Excel 2016, and that with Office 365 click-to-run significant new features can appear in between releases, so this is not a definitive list of what Excel 2016 will be able to do at RTM but a snapshot of functionality available as of March 2015 as outlined in this document and which I’ve found from my own investigations. When I find out more, or when new functionality appears, I’ll either update this post or write a new one.

Power Query

Yesterday, in the original version of my post, I mistakenly said that Power Query was a native add-in in Excel 2016: that’s not true, it’s not an add-in at all, it’s native Excel functionality. Indeed you can see that there is no separate Power Query tab any more, and instead there is a Power Query section on the Data tab instead:


Obviously I’m a massive fan of Power Query so I’m biased, but I think this is a great move because it makes all the great Power Query functionality a lot easier to discover. There’s nothing to enable – it’s there by default – although I am a bit worried that users will be confused by having the older Data tab features next to their Power Query equivalents.

There are no new features for Power Query here compared to the latest version for Excel 2013, but that’s what I expected.

Excel Forecasting Functions

I don’t pretend to know anything about forecasting, but I had a brief play with the new Forecast.ETS function and got some reasonable results out of it as seen in the screenshot below:


Slicer Multiselect

There’s a new hammer icon on a slicer, which, when you click it, changes the way selection works. The default behaviour is the same as Excel 2013: every time you click on an item, that item is selected and any previous selection is lost (unless you were holding control or shift to multiselect). However with the hammer icon selected each new click adds the item to the previously selected items. This is meant to make slicers easier to use with a touch-screen.


Time Grouping in PivotTables

Quite a neat feature this, I think. If you have a table in the Excel Data Model that has a column of type date in it, you can add extra calculated columns to that table from within a PivotTable to group by things like Year and Month. For example, here’s a PivotTable I built on a table that contains just dates:


Right-clicking on the field containing the dates and clicking Group brings up the following dialog:


Choosing Years, Quarters and Months creates three extra fields in the PivotTable:


And these fields are implemented as calculated columns in the original table in the Excel Data Model, with DAX definitions as seen here:


Power View on SSAS Multidimensional

At-bloody-last. I haven’t installed SSAS on the VM I’m using for testing Excel 2016, but I assume it just works. Nothing new in Power View yet, by the way.

Power Map data cards

Not sure why this is listed as new in Excel 2016 when it seems to be the same feature that appeared in Excel 2013 Power Map recently:

Power Pivot

There isn’t any obvious new functionality in the Power Pivot window, but it’s clear that the UI in general and the DAX formula editor experience in particular has been improved.


Suggested Relationships

When you use fields from two Excel Data Model tables that have no relationship between them in a PivotTable, you get a prompt to either create new relationships yourself or let Excel detect the relationships:


Renaming Tables and Fields in the Power Pivot window

In Excel 2013 when you renamed tables or fields in the Excel Data Model, any PivotTables that used those objects had them deleted. Now, in Excel 2016, the PivotTable retains the reference to table or field and just displays the new name. What’s even better is that when you create a measure or a calculated column that refers to a table or column, the DAX definition of the measure or calculated column gets updated after a rename too.


There are lots of new DAX functions in this build. With the help of the mdschema_functions schema rowset and Power Query I was able to compare the list of DAX functions available in 2016 with those in 2013 and create the following list of new DAX functions and descriptions:

DATEDIFF			Returns the number of units (unit specified in Interval) 
			between the input two dates
CONCATENATEX		Evaluates expression for each row on the table, then 
			return the concatenation of those values in a single string 
			result, separated by the specified delimiter
KEYWORDMATCH		Returns TRUE if there is a match between the 
			MatchExpression and Text. 
ADDMISSINGITEMS		Add the rows with empty measure values back.
CALENDAR			Returns a table with one column of all dates between 
			StartDate and EndDate 
CALENDARAUTO		Returns a table with one column of dates 
			calculated from the model automatically
CROSSFILTER		Specifies cross filtering direction to be used in 
			the evaluation of a DAX expression. The relationship is 
			defined by naming, as arguments, the two columns that 
			serve as endpoints
CURRENTGROUP		Access to the (sub)table representing current 
			group in GroupBy function. Can be used only inside GroupBy 
GROUPBY			Creates a summary the input table grouped by the 
			specified columns
IGNORE			Tags a measure expression specified in the call to 
			SUMMARIZECOLUMNS function to be ignored when 
			determining the non-blank rows.
ISONORAFTER		The IsOnOrAfter function is a boolean function that 
			emulates the behavior of Start At clause and returns 
			true for a row that meets all the conditions mentioned as 
			parameters in this function.
NATURALINNERJOIN		Joins the Left table with right table using the 
			Inner Join semantics
NATURALLEFTOUTERJOIN	Joins the Left table with right table 
			using the Left Outer Join semantics
ROLLUPADDISSUBTOTAL		Identifies a subset of columns specified 
			in the call to SUMMARIZECOLUMNS function that should be 
			used to calculate groups of subtotals
ROLLUPISSUBTOTAL		Pairs up the rollup groups with the column 
SELECTCOLUMNS		Returns a table with selected columns from the table 
			and new columns specified by the DAX expressions
SUBSTITUTEWITHINDEX		Returns a table which represents the semijoin of two 
			tables supplied and for which the common set of 
			columns are replaced by a 0-based index column. 
			The index is based on the rows of the second table 
			sorted by specified order expressions.
SUMMARIZECOLUMNS		Create a summary table for the requested 
			totals over set of groups.
GEOMEAN			Returns geometric mean of given column 
GEOMEANX			Returns geometric mean of an expression 
			values in a table.
MEDIANX			Returns the 50th percentile of an expression 
			values in a table.
PERCENTILE.EXC		Returns the k-th (exclusive) percentile of 
			values in a column.
PERCENTILE.INC		Returns the k-th (inclusive) percentile of 
			values in a column.
PERCENTILEX.EXC		Returns the k-th (exclusive) percentile of an 
			expression values in a table.
PERCENTILEX.INC		Returns the k-th (inclusive) percentile of an 
			expression values in a table.
PRODUCT			Returns the product of given column reference.
PRODUCTX			Returns the product of an expression 
			values in a table.
XIRR			Returns the internal rate of return for a schedule of 
			cash flows that is not necessarily periodic
XNPV			Returns the net present value for a schedule of cash flows

Plenty of material for future blog posts there, I think – there are lots of functions here that will be very useful. I bet Marco and Alberto are excited…


We now have support for working with Power Query in VBA.

Web Services And POST Requests In Power Query

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:

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:


    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",


    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(

                    "" & BingMapsKey,


    //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}})




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
  • The important step is GetElevations. The code is:


    "" & BingMapsKey,


    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:


You can download the sample workbook here.

Pretty Patterns With Power Query And Power Map

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:


    //declare function to draw a circle

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


    //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{_}), 


    //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}})






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


    //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)




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.

Power Map Is Released

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

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:

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: