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:

DataTab

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:

image

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.

Slicer

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:

Group1

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

Group2

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

Group3

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

Group4

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:

https://support.office.com/en-za/article/Customize-a-data-card-in-Power-Map-797ab684-82e0-4705-a97f-407e4a576c6e

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.

image

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:

image

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.

DAX

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…

VBA

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

37 thoughts on “What’s New In The Excel 2016 Preview For BI?

  1. New DAX functions are interesting, but there is more than that 🙂 I just hope Microsoft publish some decent documentation about new DAX features (so that we can start talking about that).
    One thought: what happens when you create a DAX measure using a new DAX function with Excel 2016, save the file and then open it in Excel 2013?
    Unfortunately I already know the answer…

      • There’s enough in there to reverse engineer it if you know what you’re doing. 🙂 Not having any insight beyond public blog posts, I can see what is going on.

        Pointed profiler to the tabular engine running in the background and WOW. There is tabular model metadata now returned with TMSCHEMA_ rowsets! CreateTabular events! No more UDM.

        Also you can tell from the data providers no more OLEDB. Both a blessing and a curse. They should ship the .Net Framework provider for SQL Server in the box so it just works.

        You guys should be able to discuss publically the things that can reasonably be reverse engineered. Good luck with that conversation.

      • My comments were meant for the Power BI designer, not this post. I haven’t installed the 2016 preview yet. I wonder though if the two engines are compatible…

  2. Hey Chris,

    We had VBA support for the Data Model in Excel 2013. Can’t create anything, but you can read some limited things and refresh the model.

    As a fellow fan of Power Query, I think it’s a mistake to leave the Get External Data group front and centre like it is. It needs to stay, for certain, but I really think it should be collapsed to a single menu to give more room to the Power Query functionality. It’s the future, let’s embrace it now.

    • Hi Ken,

      You’re right about the VBA, of course. For some reason I thought I saw more new stuff that is actually there, but in fact it’s all pretty much what is present in 2013. I’ve updated the post accordingly.

  3. Is any of this limited by the version of Excel 2016 someone is running? I couldn’t quite determine that from the write-up. It’s logical that this would all be part of Office365 Pro, but is it also available in all desktop versions of Excel? Only Excel Pro Plus (or whatever it’s called)? I’d love to see this all be available in the desktop editions and not limit some features to Office 365 when the desktop edition is completely capable of running that. I can understand features that require Sharepoint or the Office 365 capabilities, but would hope everything else could be in desktop.

    • I genuinely don’t know. I think the Preview is limited to Office 365 subscribers; I have a ProPlus level subscription but I don’t know what’s available for other SKUs.

  4. Thanks for sharing. Do you know what the new join types do? Does this mean power pivot will support joins other than 1 to many using DAX?

    • I haven’t been able to get Power View working yet in 2016. I got this same error when trying to build a Power View report off of a normal Excel table.

    • Come on Jesús : You had to resort to the Cloud? :):)

      (Mine is working smoothly on a Hyper V local VM)

    • Hello Jesús, if you’d be so kind, I’d like to investigate that error with you.
      If you’re willing, please email me at yitzhaks at microsoft dot com.

      • Anyone know if this fix has been released yet? I’m still bumping into the issue – not sure if it’s just me.

        To say that I’m super excited about this feature is an understatement

      • Hi Steve. I am sorry, but this update hasn’t been released yet. You’ll see the Excel version change to 16.0.3930.* when you get the update.

  5. Hi Chris,
    just one additional point to your Power Map comment. There is a new feature! A really nice one. Just as a reminder, Power Map team published this video some weeks ago:

    Now in 2016 you can import your own regions or polygons! That feature is quite cool. Unfortunately I have not yet found a way to make it work and in web there is no manual or something else available. Maybe you will find a way.

    Thanks and regards

  6. Now in 2016 you can import your own regions or polygons! That feature is quite cool. Unfortunately I have not yet found a way to make it work and in web there is no manual or something else available. Maybe you will find a way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s