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.

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.