## NaturalInnerJoin And NaturalLeftOuterJoin DAX Functions In Excel 2016

Continuing my series on new DAX functions in Excel 2016, here are two more: NaturalInnerJoin() and NaturalLeftOuterJoin(). Both do pretty much what you’d expect.

Consider the following two tables in an Excel worksheet, called ColourFruit and FruitPrice:

With these tables loaded into the Excel Data Model as linked tables, the next step is to create a relationship between the tables on the Fruit column:

Both functions only work with two tables that have an active relationship between them, and both take two tables from the Excel Data Model as parameters. Once you’ve done that you can use these functions in a DAX query.

The queries

evaluate naturalinnerjoin(ColourFruit,FruitPrice)

and

evaluate naturalinnerjoin(FruitPrice,ColourFruit)

…both perform an inner join between the two tables on the Fruit column and both return the same table:

The query

evaluate naturalleftouterjoin(ColourFruit,FruitPrice)

…returns

The query

evaluate naturalleftouterjoin(FruitPrice,ColourFruit)

…returns:

For NaturalLeftOuterJoin() the table given in the first parameter is on the left-hand side of the left outer join, so all rows from it are returned, whereas the table in the second parameter is on the right-hand side of the join so only the matching rows are returned.

## Benford’s Law And Power Query

Probably my favourite session at SQLBits the other week was Professor Mark Whitehorn on exploiting exotic patterns in data. One of the things he talked about was Benford’s Law, something I first heard about several years ago (in fact I’m sure I wrote a blog post on implementing Benford’s Law in MDX but I can’t find it), about the frequency distribution of digits in data. I won’t try to explain it myself but there are plenty of places you can read up on it, for example: http://en.wikipedia.org/wiki/Benford%27s_law . I promise, it’s a lot more interesting that it sounds!

Anyway, it struck me that it would be quite useful to have a Power Query function that could be used to find the distribution of the first digits in any list of numbers, for example for fraud detection purposes. The first thing I did was write a simple query that returned the expected distributions for the digits 1 to 9 according to Benford’s Law:

let //function to find the expected distribution of any given digit Benford = (digit as number) as number => Number.Log10(1 + (1/digit)), //get a list of values between 1 and 9 Digits = {1..9}, // get a list containing these digits and their expected distribution DigitsAndDist = List.Transform(Digits, each {_, Benford(_)}), //turn that into a table Output = #table({"Digit", "Distribution"}, DigitsAndDist) in Output

Next I wrote the function itself:

//take a single list of numbers as a parameter (NumbersToCheck as list) as table=> let //remove any non-numeric values RemoveNonNumeric = List.Select(NumbersToCheck, each Value.Is(_, type number)), //remove any values that are less than or equal to 0 GreaterThanZero = List.Select(RemoveNonNumeric, each _>0), //turn that list into a table ToTable = Table.FromList(GreaterThanZero, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RenameColumn = Table.RenameColumns(ToTable,{{"Column1", "Number"}}), //function to get the first digit of a number FirstDigit = (InputNumber as number) as number => Number.FromText(Text.Start(Number.ToText(InputNumber),1))-1, //get the distributions of each digit GetDistributions = Table.Partition(RenameColumn, "Number", 9, FirstDigit), //turn that into a table DistributionTable = Table.FromList(GetDistributions, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //add column giving the digit AddIndex = Table.AddIndexColumn(DistributionTable, "Digit", 1, 1), //show how many times each first digit occurred CountOfDigits = Table.AddColumn(AddIndex, "Count", each Table.RowCount([Column1])), RemoveColumn = Table.RemoveColumns(CountOfDigits ,{"Column1"}), //merge with table showing expected distributions Merge = Table.NestedJoin(RemoveColumn,{"Digit"}, Benford,{"Digit"},"NewColumn",JoinKind.Inner), ExpandNewColumn = Table.ExpandTableColumn(Merge, "NewColumn", {"Distribution"}, {"Distribution"}), RenamedDistColumn = Table.RenameColumns(ExpandNewColumn, {{"Distribution", "Expected Distribution"}}), //calculate actual % distribution of first digits SumOfCounts = List.Sum(Table.Column(RenamedDistColumn, "Count")), AddActualDistribution = Table.AddColumn(RenamedDistColumn, "Actual Distribution", each [Count]/SumOfCounts) in AddActualDistribution

There’s not much to say about this code, apart from the fact that it’s a nice practical use case for the Table.Partition() function I blogged about here. It also references the first query shown above, called Benford, so that the expected and actual distributions can be compared.

Since this is a function that takes a list as a parameter, it’s very easy to pass it any column from any other Power Query query that’s in the same worksheet (as I showed here) for analysis. For example, I created a Power Query query on this dataset in the Azure Marketplace showing the number of minutes that each flight in the US was delayed in January 2012. I then invoked the function above, and pointed it at the column containing the delay values like so:

The output is a table (to which I added a column chart) which shows that this data follows the expected distribution very closely:

You can download my sample workbook containing all the code from here.

## ConcatenateX() DAX Function In Excel 2016

This is the first of many posts on the new DAX functions that have appeared in Excel 2016 (for a full list see this post). Today: the ConcatenateX() function.

The mdschema_functions schema rowset gives the following description of this function:

*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*

Its signature is:

CONCATENATEX(Table, Expression, [Delimiter])

It’s easier to understand what it does using a simple example though. Consider the following table on a worksheet in Excel 2016:

When you add this table to the Excel Data Model (I called the table Sales) you can add the following measure:

Purchasing Customers:= CONCATENATEX( VALUES(Sales[Customer]), Sales[Customer], "," )

If you then use this measure in a PivotTable, you see the following:

As you can see, the measure returns a comma-delimited list of all of the customers who have bought each product. Very useful…

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

**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**

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

## Power BI And Excel 2016 BI News

There have been quite a few Power BI and Office BI-related announcements over the last few weeks, and while I’ve tweeted about them (I’m @Technitrain if you’re not following me already) I though it would be a good idea to summarise them all in one post.

**Power BI Announcements at Convergence and SQLBits**

You’ve probably already seen the announcement today on the Power BI blog that Power BI is FINALLY available to those of us outside the USA:

http://blogs.msdn.com/b/powerbi/archive/2015/03/16/power-bi-preview-now-available-worldwide.aspx

At last! I’m sure MS had very good reasons why they couldn’t make the Power BI Preview available worldwide back in December, but this decision caused a lot of frustration in the MS BI community and I hope it’s not something that happens again. I can also confirm that the Power BI iPhone app is now available in the UK as well. The new data sources for Power BI that are coming soon – especially Google Analytics – will be very popular I think.

While I’m on the topic of Power BI, a few interesting nuggets about upcoming functionality emerged at SQLBits last week. Kasper mentioned that there will be some new DAX functions appearing in Power BI soon: Median, Percentile, DateDiff and XPNV. Presumably they will appear when we get the ability to create DAX measures and calculated columns in the Power BI Dashboard Designer. Also, following on from the bidirectional relationships functionality I blogged about earlier this year, there was the news that Power BI will also understand 1:1 relationships as well as 1:many, many:1 and many:many.

**Office 2016 Preview BI Features**

The Office 2016 preview went public today too:

http://blogs.office.com/2015/03/16/announcing-the-office-2016-it-pro-and-developer-preview/

There’s a great overview of what’s new for BI in Office 2016 here:

The main points are:

- Power Query is now a native feature of Excel 2016.
- Power View works on SSAS Multidimensional (this is only going to work on the versions of SSAS Multidimensional that support DAX queries, ie SSAS 2014 or SSAS 2012 SP2)
- New Excel forecasting functions
- Time grouping functionality in PivotTables

I’ll be writing a more detailed blog on all of this at some point soon, once I know what’s officially public and what isn’t.

The Power Query announcement is interesting because, as things stand at the moment, we’ll be able to use full Power Query, Power Pivot and Power View functionality for free in the Power BI Dashboard Designer, but in Excel the same functionality is restricted to users of the Professional Plus SKUs. This is crazy, and I hope Microsoft makes the Power add-ins available for every SKU of Excel 2016. Have you signed the petition for this yet?

**Power Map**

Last week the Power Map team released a new video showcasing functionality from an upcoming release:

https://www.youtube.com/watch?v=aP-vZfC3Fd4&feature=youtu.be

Although there are no details about what is shown in the video, it certainly looks like the ability to use custom shapes (the main missing feature in Power Map up to now) will be coming soon.

Wow, psychedelic…

**Surface Hub**

Finally, BI is clearly one of the main use-cases of the new Surface Hub (see also this video):

I wonder if I can justify buying one for demo purposes?

## SSAS Multidimensional Cube Design Video Training

I’ve been teaching my SSAS Cube Design training course for several years now (there are still a few places free for the London course next month if you’re interested) and I have now recorded a video training version of it for Project Botticelli.

The main page for the course is here:

https://projectbotticelli.com/cubes?pk_campaign=tt2015cwb

There’s also a free, short video on using the SSAS Deployment Wizard that you can see here:

If you register before the end of March using the code **TECHNITRAIN2015MARCH** you’ll get a 15% discount.

## Using Excel Slicers To Pass Parameters To Power Query Queries

Power Query is great for filtering data before it gets loaded into Excel, and when you do that you often need to provide a friendly way for end users to choose what data gets loaded exactly. I showed a number of different techniques for doing this last week at SQLBits but here’s my favourite: using Excel slicers.

Using the Adventure Works DW database in SQL Server as an example, imagine you wanted to load only only rows for a particular date or set of dates from the FactInternetSales table. The first step to doing this is to create a query that gets all of the data from the DimDate table (the date dimension you want to use for the filtering). Here’s the code for that query – there’s nothing interesting happening here, all I’m doing is removing unnecessary columns and renaming those that are left:

let Source = Sql.Database("localhost", "adventure works dw"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data], #"Removed Other Columns" = Table.SelectColumns(dbo_DimDate, {"DateKey", "FullDateAlternateKey", "EnglishDayNameOfWeek", "EnglishMonthName", "CalendarYear"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{ {"FullDateAlternateKey", "Date"}, {"EnglishDayNameOfWeek", "Day"}, {"EnglishMonthName", "Month"}, {"CalendarYear", "Year"}}) in #"Renamed Columns"

Here’s what the output looks like:

Call this query Date and then load it to a table on a worksheet. Once you’ve done that you can create Excel slicers on that table (slicers can be created on tables as well as PivotTables in Excel 2013 but not in Excel 2010) by clicking inside it and then clicking the Slicer button on the Insert tab of the Excel ribbon:

Creating three slicers on the Day, Month and Year columns allows you to filter the table like so:

The idea here is to use the filtered rows from this table as parameters to control what is loaded from the FactInternetSales table. However, if you try to use Power Query to load data from an Excel table that has any kind of filter applied to it, you’ll find that you get all of the rows from that table. Luckily there is a way to determine whether a row in a table is visible or not and I found it in this article written by Excel MVP Charley Kyd:

http://www.exceluser.com/formulas/visible-column-in-excel-tables.htm

You have to create a new calculated column on the table in the worksheet with the following formula:

=(AGGREGATE(3,5,[@DateKey])>0)+0

This calculated column returns 1 on a row when it is visible, 0 when it is hidden by a filter. You can then load the table back into Power Query, and when you do you can then filter the table in your new query so that it only returns the rows where the Visible column contains 1 – that’s to say, the rows that are visible in Excel. Here’s the code for this second query, called SelectedDates:

let Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, each ([Visible] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Visible"}) in #"Removed Columns"

This query should not be loaded to the Excel Data Model or to the worksheet.

Next, you must use this table to filter the data from the FactInternetSales table. Here’s the code for a query that does that:

let Source = Sql.Database("localhost", "adventure works dw"), dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data], #"Removed Other Columns" = Table.SelectColumns(dbo_FactInternetSales, {"ProductKey", "OrderDateKey", "CustomerKey", "SalesOrderNumber", "SalesOrderLineNumber", "SalesAmount", "TaxAmt"}), Merge = Table.NestedJoin(#"Removed Other Columns",{"OrderDateKey"}, SelectedDates,{"DateKey"},"NewColumn",JoinKind.Inner), #"Removed Columns" = Table.RemoveColumns(Merge, {"ProductKey", "OrderDateKey", "CustomerKey"}), #"Expand NewColumn" = Table.ExpandTableColumn(#"Removed Columns", "NewColumn", {"Date"}, {"Date"}), #"Reordered Columns" = Table.ReorderColumns(#"Expand NewColumn", {"Date", "SalesOrderNumber", "SalesOrderLineNumber", "SalesAmount", "TaxAmt"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{ {"SalesOrderNumber", "Sales Order Number"}, {"SalesOrderLineNumber", "Sales Order Line Number"}, {"SalesAmount", "Sales Amount"}, {"TaxAmt", "Tax Amount"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}) in #"Changed Type"

Again, most of what this query does is fairly straightforward: removing and renaming columns. The important step where the filtering takes place is called Merge, and here the data from FactInternetSales is joined to the table returned by the SelectedDates query using an inline merge (see here for more details on how to do this):

The output of this query is a table containing rows filtered by the dates selected by the user in the slicers, which can then be loaded to a worksheet:

The last thing to do is to cut the slicers from the worksheet containing the Date table and paste them onto the worksheet containing the Internet Sales table:

You now have a query that displays rows from the FactInternetSales table that are filtered according to the selection made in the slicers. It would be nice if Power Query supported using slicers as a data source direct without using this workaround and you can vote for it to be implemented here.

You can download the sample workbook for this post here.