Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Conditional logic in Power Query

with 8 comments

Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point:

image

The following query shows how to use an if … then … else statement to test whether the value from the table is equal to 5:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    IfStatement = if InputValue=5 

                    then "The number is five" 

                    else "The number is not five"

in

    IfStatement

What about more complex conditional logic? The M language doesn’t include anything like a case statement, but it is possible to write the equivalent of one quite easily.

Here’s an example of a simple case statement:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    CaseValues = {

                    {1, "First"},

                    {2, "Second"},

                    {3, "Third"},

                    {4, "Fourth"},

                    {5, "Fifth"},

                    {InputValue, "Else condition"}

                },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}

in

    SimpleCase

This works as follows:

  • The CaseValues step defines a list containing six items, each of which is itself a list containing a number and some text. The number is the value to compare to the input value, and the text is what will be returned if the number does match the input value.
  • The last item in the CaseValues list contains the input value, so this will be returned where the input value matches none of the preceding values
  • The SimpleCase step uses List.Select() to filter the list in CaseValues so that only the items in the list where the input value matches the number in the list.
  • Since List.Select itself returns a list, this list is then passed to List.First() to get the first item in the list returned by List.Select (there should only be one item in the list in this particular query), and then {1} returns the text from that item. This is the output of the query.

You can write a searched case expression in a very similar way, by declaring functions that return boolean values instead of using numbers as follows:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    CaseValues = {

    { (x)=>x<10, "Less than 10"},

    { (x)=>x<20, "Less than 20"},

    { (x)=>x<30, "Less than 30"},

    { (x)=>x<40, "Less than 40"},

    { (x)=>x<50, "Less than 50"},

    { (x)=>true, "Else condition"}

    },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}(InputValue))){1}

in

    SimpleCase

 

In this query the CaseValues step contains a list of lists, where each item in the list consists of list containing a function and a text value. List.Select calls each function and only returns the items where the function returns true, and finally the text from the first item that List.Select returns is the output of the query.

You can download the sample workbook here.

Written by Chris Webb

March 10, 2014 at 9:00 am

Posted in Power Query

SQLBits XII

leave a comment »

In case you missed the announcement yesterday, SQLBits XII will be taking place at the International Centre, Telford, UK on July 17th-19th. SQLBits is the biggest SQL Server and Microsoft BI conference in Europe and will feature precons and sessions from some of the best-known SQL Server experts in the world (I see Brent Ozar and Brian Knight have already submitted sessions, which is cool). And apart from all the amazing technical content it’s a lot of fun – just ask anyone who’s been to a previous SQLBits!

Full details and the link to register can be found on the SQLBits website: http://sqlbits.com/ Hope to see you there…

Written by Chris Webb

March 4, 2014 at 9:50 am

Posted in Events

Allocation in Power Query, Part 2

with 2 comments

Last week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with a post that deals with a slightly more advanced (and more realistic) scenario: what happens if the contracts you are working with don’t all start on the same date?

Here’s the table of data that is the starting point for my examples:

image

I’ve made two changes:

  • I’ve added a contract name to serve as a primary key so I can uniquely identify each contract in the table. Several people asked me why I added index columns to my tables after my last post and this is why: without a way of uniquely identifying contracts I might end up aggregating values for two different contracts that happen to have the same number of months, contract amount and start date.
  • I’ve added a contract start date column which contains the date that the contract starts on, which is always the first day of a month.

Now let’s imagine that you want to make each monthly payment on the last day of the month. You need to take each contact and, for each monthly payment generate a row containing the date that is the last day of the month, containing the allocated payment amount.

Once again, having have opened the Query Editor the first step is to calculate the amount of the monthly payment using a custom column that divides Contract Amount by Months in Contract. This is shown in the Allocated Amount column:

image

Now to generate those monthly payment rows. Since this is reasonably complex I decided to declare a function to do this called EndsOfMonths inside the query, as follows:

= (StartDate, Months) =>
List.Transform(List.Numbers(1, Months), each Date.AddDays(Date.AddMonths(StartDate, _ ), -1))

This function takes the start date for contract and the number of months, and:

  • Uses List.Numbers() to create a list containing numbers from 1 to the number of months in the contract. For example if there were three months in the contract, this would return the list {1,2,3}
  • This list is then passed to List.Transform(), and for each item in the list it does the following:
    • Adds the given number of months to the start date, then
    • Subtracts one day from that date to get the payment date, which will be the last day of the month it is in

Calling this function on each row of the table in a new custom column (called Payment Date here) gives you a list of the payment dates for each contract:

image

All that you need to do then is to click on the Expand icon next to the Payment Date column header and make sure each column has the correct type, and you have your output for loading into the Excel Data Model:

image

Here’s the code for the query:

let

    //Load source data from Excel table

    Source = Excel.CurrentWorkbook(){[Name="Contract"]}[Content],

    //Add custom column for Allocated Amount

    InsertedCustom1 = Table.AddColumn(Source, "Allocated Amount", 

        each [Contract Amount]/[Months In Contract]),

    //Declare function for returning a list of payment dates

    EndsOfMonths = (StartDate, Months) => 

        List.Transform(List.Numbers(1, Months), 

            each Date.AddDays(Date.AddMonths(StartDate, _ ), -1)),

    //Call this function for each contract in a new custom column

    InsertedCustom = Table.AddColumn(InsertedCustom1, "Payment Date", 

        each EndsOfMonths([Contract Start Date], [Months In Contract]) ),

    //Expand the list

    #"Expand Payment Date" = Table.ExpandListColumn(InsertedCustom, "Payment Date"),

    //Set column data types

    ChangedType = Table.TransformColumnTypes(#"Expand Payment Date",

        {{"Contract Start Date", type date}, 

        {"Payment Date", type date}, {"Allocated Amount", type number}, 

        {"Contract Amount", type number}, {"Months In Contract", type number}})

in

    ChangedType

 
There’s one more thing to do though. Since the Contract table contains real dates, it’s a very good idea to have a separate Date table in the Excel Data Model to use with it. I’ve already blogged about how to use a function to generate a Date table in Power Query (as has Matt Masson, whose version adds some extra features) and in that function (called CreateDateTable) can be reused here. Here’s a query that returns a Date table starting at the beginning of the year of the earliest start date in the contract table and ends at the end of the year of the last payment date:
 
let

    //Aggregate the table to find the min contract start date

    //and the max payment date

    GroupedRows = Table.Group(Contract, {}, 

    {{"Min Start Date", each List.Min([Contract Start Date]), type datetime}, 

    {"Max Payment Date", each List.Max([Payment Date]), type datetime}}),

    //Find the first day of the year of the min start date    

    #"Start Date" = DateTime.Date(Date.StartOfYear(GroupedRows{0}[Min Start Date])),

    //Find the last day of the year of the max payment date

    #"End Date" = DateTime.Date(Date.EndOfYear(GroupedRows{0}[Max Payment Date])),

    //Call CreateDateTable with these parameters

    DateTable = CreateDateTable(#"Start Date", #"End Date"),

    //Change data types

    ChangedType = Table.TransformColumnTypes(DateTable,{{"MonthNumberOfYear", type number}

    , {"DayOfWeekNumber", type number}})

in

    ChangedType

 

You can now build a PivotTable to show the payments allocated over the correct ranges:

image

The sample workbook can be downloaded here.

Written by Chris Webb

March 2, 2014 at 9:50 pm

Posted in Uncategorized

Power Map Is Released

with 5 comments

Power Map was released as part of Office 2013 SP1. You can read the announcements here:

http://blogs.msdn.com/b/powerbi/archive/2014/02/25/power-map-for-excel-now-generally-available-automatically-updated-for-office-365.aspx

http://blogs.technet.com/b/office_sustained_engineering/archive/2014/02/25/announcing-the-release-of-service-pack-1-for-office-2013-and-sharepoint-2013.aspx?WT.mc_id=blog_PBI_GA_PowerMap

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:

http://office.microsoft.com/en-us/excel-help/power-map-for-excel-HA104204034.aspx?redir=0

I quote:

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:

http://datasavvy.wordpress.com/2014/02/25/power-map-for-excel-is-now-generally-available-for-office-365-with-a-few-new-features-and-bug-fixes/

 

 

Written by Chris Webb

February 25, 2014 at 9:24 pm

Posted in Power Map

Allocation in Power Query

with 12 comments

Now that the brave new world of self-service BI is upon us, old-school corporate BI types like me need to sharpen our Excel skills – and anyone learning Excel will, sooner or later, end up on Bill Jelen (aka Mr Excel)’s site. I found his latest podcast on splitting the value of a contract over N months particularly interesting not only because I had to deal with a similar problem with a client only a few weeks ago but also because the problem of allocation in Power Query is something I’ve been meaning to blog about for a while. In this post I’m going to take the same data that Bill and Mike Girvin (whose book on Excel array formulas I got for Christmas!) used in the podcast and show how to achieve the same results they did but in Power Query and Power Pivot.

My starting point is an Excel sheet with two tables named Contract and Month, shown below:

image

It’s not exactly the same layout as in the podcast but that’s deliberate – I want to keep my source data and my output (which could be a PivotTable, cube formulas or a Power View sheet) separate.

Next, I import the Month table into Power Query using the From Table button and then click on the Add Index button to add an index column, so that the query output is as follows:

image

I don’t need to load this anywhere though, even though I’m going to use its output in the next query, so I leave both of the boxes in the Load Settings section of the Query Editor unchecked and go back to the worksheet:

image

Next, I import the Contract table and add an index column in the same way:

image

I can now add a custom column to calculate the monthly amount by dividing Contract Amount by Months In Contract:

image

Now comes the interesting bit. I insert another custom column and this time the M expression to paste into the dialog is:

Table.FirstN(Month, [Months In Contract])

In each row this column contains a table containing the first N rows of the Month table, where N is the value from the [Months In Contract] column. The output is this:

image

I then just need to click on the expand icon next to the column header of the Custom column to repeat each contract row for all the months it applies to, rename the columns and set the column types appropriately, and I’m ready to load into the Excel Data Model:

image

Here’s the full M code for both queries:

--Month Query

let

    Source = Excel.CurrentWorkbook(){[Name="Month"]}[Content],

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    ReorderedColumns = Table.ReorderColumns(InsertedIndex,{"Index", "Month"})

in

    ReorderedColumns


--Contract Query

let

    Source = Excel.CurrentWorkbook(){[Name="Contract"]}[Content],

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    RenamedColumns = Table.RenameColumns(InsertedIndex,{{"Index", "ContractID"}}),

    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"ContractID",

                            "Months In Contract", "Contract Amount"}),

    InsertedCustom = Table.AddColumn(ReorderedColumns, "Allocated Amount",

                            each [Contract Amount]/[Months In Contract]),

    InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom",

                            each Table.FirstN(Month, [Months In Contract])),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom1, "Custom",

                            {"Index", "Month"}, {"Custom.Index", "Custom.Month"}),

    RenamedColumns1 = Table.RenameColumns(#"Expand Custom",{{"Custom.Index", "MonthID"},

                            {"Custom.Month", "Month"}}),

    ChangedType = Table.TransformColumnTypes(RenamedColumns1,{{"Allocated Amount", type number},

                            {"Contract Amount", type number}, {"MonthID", type number},

                            {"Months In Contract", type number}, {"ContractID", type number}})

in

    ChangedType

 

Last of all, I need to go into the Power Pivot window and do two things:

  • Use the Sort By Column functionality to sort my Month column by MonthID
  • Format my Allocated Amount column using a dollar sign

And I’m ready! I can now create a PivotTable containing my allocated values:

image

Maybe it’s a little bit more long-winded than Bill or Mike’s examples but I don’t think it’s any more complex. And of course, now the data is in the Excel Data Model I have a lot more flexibility on how to present the data. For example I can use Power View with no remodelling or formula changes necessary:

image

One last point: I know it’s good practise to use a separate Date table with Power Pivot. I didn’t do so here because I wanted to keep as close to the original example as possible. And because I’m lazy.

You can download the demo workbook here.

Written by Chris Webb

February 24, 2014 at 9:10 pm

Posted in Power Query

“Expert Cube Development” Second Edition Available Soon!

with 7 comments

Within a matter of days, “Expert Cube Development with SSAS 2012 Multidimensional Models” will be published. It’s the second edition of the very successful (19 5* reviews on Amazon US as of now) book on SSAS cube development that Marco, Alberto and I wrote a few years ago, updated for SSAS 2012.

image

You can pre-order it now from the Packt website, Amazon US or Amazon UK.

Before you rush off to order a copy, there are a three things I’d like to point out:

  1. This is basically the same book as the first edition with updated screenshots, a few bugs fixed, and several sections updated/expanded for SSAS 2012. There are no substantial changes. If you already have a copy of the first edition it’s probably not worth buying a copy of the second edition.
  2. The book only covers SSAS Multidimensional models, it does not cover SSAS Tabular models.
  3. This is not a basic introduction to building SSAS cubes – it’s aimed at intermediate-level SSAS developers who are already familiar with cubes, dimensions and MDX and who want to learn about best practices, design patterns, performance tuning and (most importantly) which features work well and which ones don’t. If you like the material I post here on my blog, you’ll probably like the book.

If you’re OK with that then by all means, go ahead and get your wallet out!

Written by Chris Webb

February 18, 2014 at 4:08 pm

How Many Partitions Per Measure Group Are Allowed in SSAS Standard Edition?

with 4 comments

Most people know that the ability to partition a measure group is a feature of Analysis Services Multidimensional Enterprise Edition, but that doesn’t mean that in Standard Edition you are limited to just having one partition per measure group. In fact it is possible to use up to three partitions per measure group in SSAS Multidimensional SE, with some limitations. For a long time I wasn’t sure whether this was legal, as opposed to possible, according to the terms of the SQL Server licence but since this page in Books Online (thanks to Rob Kerr for the link) states that you can have up to three partitions in SE then I assume it is:

http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx#BISemModel_multi

HOWEVER

If you do decide to use more than one partition in SSAS SE then you do need to understand the risks involved – and the reason I wanted to write this post is because I see a lot of people using more than one partition per measure group in SE without understanding those risks. Strictly speaking, SE is only designed to work with one partition per measure group. It needs those extra two partitions for two pieces of functionality:

  1. To support writeback, because using this feature requires SSAS to create a separate partition to hold writeback values
  2. To support incremental processing, because when you do incremental processing on a partition in the background SSAS needs to create a new partition, process it and then merge it with your existing partition

Therefore if you create more than one partition per measure group in SE you may find that writeback and/or incremental processing will break.

Written by Chris Webb

February 12, 2014 at 10:34 am

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers