Archive for the ‘Power Map’ Category
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.
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:
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:
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.
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:
FUNCTION NAME DESCRIPTION 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 function. 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 added by ROLLUPADDISSUBTOTAL 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 reference. 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…
It looks like we have support for the Excel Data Model (aka Power Pivot) in VBA at last. I need to do some research here, but I get the distinct feeling that the only things that are possible through VBA are the things you can do in the Excel ribbon, such as creating connections, tables and relationships. I can’t see any support for creating measures, calculated columns or hierarchies…? I can’t see anything relating to Power Query either. Maybe I’m not looking in the right place; maybe something will come in a later build?
UPDATE: I’m an idiot – there is one minor change to the VBA support for the Excel Data Model, but actually almost everything that I see in 2016 is also present in 2013. Sorry…
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:
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:
"http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & 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.
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:
I then created another Power Query query to call this function 30 times to create 30 circles with different radiuses:
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 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:
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:
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:
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:
In Power Map you can create a new layer type of Region and see each country shaded by their sales value:
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):
- You now have the option of seeing a ‘Flat Map’. Here’s the map above shown in flattened form:
- You can now control the colours used in a layer:
- You can add annotations with images in:
- 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.