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:
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.
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.
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:
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:
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
We now have support for working with Power Query in VBA.
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:
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.
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:
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?
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.
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:
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.
Following on from last month’s post on ideas for new features in SSAS Multidimensional, if you are interested in telling Microsoft what features you think should be added to the on-prem SQL Server BI tools in the next version you can do so here:
Unsurprisingly, there are plenty of pleas for SSRS to get some love. My suggestion is to integrate Power Query with SSRS: it would add a lot of new data sources that SSRS desperately needs; it would add data transformation and calculation capabilities; and it would also provide the beginnings of a common developer experience for corporate and self-service BI tools – Power Query integrated with Report Builder would be a useful companion to the Power BI Dashboard Designer.
