Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M

One very common requirement when creating a Power BI report is the ability to apply a filter for the current day, week, month, quarter or year. There are several ways of implementing this: you could add relative date columns to your Date table as I show here (I used DAX calculated columns but you could also do this in M quite easily too); you could also build the filter into the DAX for your measures, although that could mean you end up with a lot of quite complex measures.

Last week I received an email asking for help with an interesting variation on this problem: how can you create a report with a single slicer that allows you to switch between showing data for the current day, week, month or year? The requirement to have a single slicer is important here: if you create new columns on the date table, that would allow you to have a single slicer that allows for selecting the current day or any relative day, or the current week or any relative week, or the current month and any relative month, or the current year and any relative year,  but it wouldn’t allow you to select weeks, months and years together in the same slicer.

Here are some screenshots showing what we want to achieve: a report with two measures and a single slicer that allows the user to switch between displaying data for a variety of relative time periods:

image

image

image

The way to achieve this is not all that different from the calculated column approach, but it requires a separate table to model the many-to-many relationship between all the required relative period selections and the dates in them, as well as the use of bidirectional cross-filtering between tables (which I blogged about here). The data model I used for this report looks like this:

image

The Sales table just contains sales data; the Date table is a normal Power BI date table. The Period table is the interesting table here: it contains one row for each combination of relative time period (eg “Today”, “Current Week To Date”, “Rolling Month”) and date:

image

It’s the Period column on this table that is used to create the slicer in the screenshots above. The Sort column is used along with Power BI’s Sort By Column functionality to make the values in the Period column appear in a sensible order in the report.

Notice also that on the relationship between the Period table and the Date table the Cross filter direction property is set to Both:

image

This means that a selection on the Period table travels up the relationship to the Date table and then back down the relationship from Date to Sales. For example, selecting “Current Week” in the Period table will select the dates in the current week on the Date table, which in turn selects the rows for those dates on the Sales table.

The challenge, though, is to write the query to populate the Period dimension. I’ve done this in two parts. First, here’s a function called CreatePeriodTable that returns a table for a single time period selection. It takes the name of the time period and a start date and end date, and will return a table with one row for each date in the date range:

(
PeriodName as text, 
StartDate as date, 
EndDate as date, 
SortOrder as number
) as table =>
let
    DayCount = Duration.Days(EndDate-StartDate)+1,
    DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddPeriodName = List.Transform(DateList, 
        each {PeriodName,_,SortOrder}),
    CreateTable = #table(
        type table[Period=text, Date=date, Sort=number], 
        AddPeriodName)
in
    CreateTable

For example, calling this function like so:

CreatePeriodTable("Demo", #date(2016,1,1), #date(2016,1,5),1)

Returns the following table (with the dates shown in dd/mm/yyyy format):

image

Second, here’s a query that calls this function once for each of the time periods you want to be able to select by and creates a single table that contains all of the rows returned by each of the function calls:

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today", 
                TodaysDate, 
                TodaysDate, 
                1},
                {"Current Week To Date", 
                Date.From(Date.StartOfWeek(TodaysDate)), 
                TodaysDate, 
                2},
                {"Current Month To Date", 
                Date.From(Date.StartOfMonth(TodaysDate)), 
                TodaysDate, 
                3},
                {"Current Year To Date", 
                Date.From(Date.StartOfYear(TodaysDate)), 
                TodaysDate, 
                4},
                {"Rolling Week", 
                Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                5},
                {"Rolling Month", 
                Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                6},
                {"Rolling Year", 
                Date.AddYears(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                7}
             },
    GetTables = List.Transform(Ranges, 
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
    
in
    Output

In this query the Ranges step contains a list of lists, where each list in the list represents a time period with its start and end dates in the same order that you’d pass these values as parameters to the CreatePeriodTable() function. I’ve deliberately structured the code to make it easy to add new time period to the list. Hopefully the example time periods in the query above give you a good idea of what’s possible in M and all the functions it gives you for calculating different dates. The GetTables step loops over this list and calls the CreatePeriodTable() function for each list in the list, and the Output step combines all the data into a single table.

All of the dates ranges here end with today’s date, as returned by the DateTimeZone.FixedUtcNow() function, but you may want to check out Ken’s post here on handling time zones in M depending on your exact requirements. Because this happens in M when the data is loaded the value of today’s date will be fixed at the point in time that data refresh took place.

You can download the example workbook for this post here.

Understanding Let Expressions In M For Power BI And Power Query

When you start writing M code for loading data in Power Query or Power BI, one of the first things you’ll do is open up the Advanced Editor for a query you’ve already built using the UI. When you do that you’ll see a very scary chunk of code (and at the time of writing there’s no intellisense or colour coding in the Advanced Editor, making it even more scary) and you’ll wonder how to make sense of it. The first step to doing so is to understand how let expressions work in M.

Each query that you create in Power BI Desktop or Power Query is a single expression that, when evaluated, returns a single value – and that single value is usually, but not always, a table that then gets loaded into the data model. To illustrate this, open up Power BI Desktop (the workflow is almost the same in Power Query), click the Edit Queries button to open the Query Editor window and then click New Source/Blank Query to create a new query.

image

Next, go to the View tab and click on the Advanced Editor button to open the Advanced Editor dialog:

image

You’ll notice that this doesn’t actually create a blank query at all, because there is some code visible in the Advanced Editor when you open it. Delete everything there and replace it with the following M expression:

"Hello " & "World"

image

Hit the Done button and the expression will be evaluated, and you’ll see that the query returns the text value “Hello World”:

image

Notice how the ABC icon next to the name of the Query – Query1 – indicates that the query returns a text value. Congratulations, you have written the infamous “Hello World” program in M!

You might now be wondering how the scary chunk of code you see in the Advanced Editor window for your real-world query could possibly be a single expression – but in fact it is. This is where let expressions come in: they allow you to break a single expression down into multiple parts. Open up the Advanced Editor again and enter the following expression:

let
    step1 = 3,
    step2 = 7,
    step3 = step1 * step2
in
    step3

image

Without knowing anything about M it’s not hard to guess that this bit of code returns the numeric value 21 (notice again that the 123 icon next to the name of the query indicates the data type of the value the query returns):

image

In the M language a let expression consists of two sections. After the let comes a list of variables, each of which has a name and an expression associated with it. In the previous example there are three variables: step1, step2 and step3. Variables can refer to other variables; here, step3 refers to both step1 and step2. Variables can be used to store values of any type: numbers, text, dates, or even more complex types like records, lists or tables; here, all three variables return numbers. The Query Editor is usually clever enough to display these variables as steps in your query and so displays then in the Applied Steps pane on the right-hand side of the screen:

image

The value that the let expression returns is given in the in clause. In this example the in clause returns the value of the variable step3, which is 21.

It’s important to understand that the in clause can reference any or none of the variables in the variable list. It’s also important to understand that, while the variable list might look like procedural code it isn’t, it’s just a list of variables that can be in any order. The UI will always generate code where each variable/step builds on the value returned by the previous variable/step but when you’re writing your own code the variables can be in whatever order that suits you. For example, the following query also returns the value 21:

let
    step3 = step1 * step2,
    step2 = 7,
    step1 = 3
in
    step3

image

The in clause returns the value of the variable step3, which in order to be evaluated needs the variables step2 and step1 to be evaluated; the order of the variables in the list is irrelevant (although it does mean the Applied Steps no longer displays each variable name). What is important is the chain of dependencies that can be followed back from the in clause.

To give another example, the following query returns the numeric value 7:

let
    step3 = step1 * step2,
    step2 = 7,
    step1 = 3
in
    step2

image

In this case, step2 is the only variable that needs to be evaluated for the entire let expression to return its value. Similarly, the query

let
    step3 = step1 * step2,
    step2 = 7,
    step1 = 3
in
    "Hello" & " World"

image

…returns the text value “Hello World” and doesn’t need to evaluate any of the variables step1, step2 or step3 to do this.

The last thing to point out is that if the names of the variables contain spaces, then those names need to be enclosed in double quotes and have a hash # symbol in front. For example here’s a query that returns the value 21 where all the variables have names that contain spaces:

let
    #"this is step 1" = 3,
    #"this is step 2" = 7,
    #"this is step 3" = #"this is step 1" * #"this is step 2"
in
    #"this is step 3"

image

How does all this translate to queries generated by the UI? Here’s the M code for a query generated by the UI that connects to SQL Server and gets filtered data from the DimDate table in the Adventure Works DW database:

let
    Source = Sql.Database("localhost", "adventure works dw"),
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, 
                                    each ([DayNumberOfWeek] = 1))
in
    #"Filtered Rows"

Regardless of what the query actually does, you can now see that there are three variables declared here, #”Filtered Rows”, dbo_DimDate and Source, and the query returns the value of the #”Filtered Rows” variable. You can also see that in order to evaluate the #”Filtered Rows” variable the dbo_DimDate variable must be evaluated, and in order to evaluate the dbo_DimDate variable the Source variable must be evaluated. The Source variable connects to the Adventure Works DW database in SQL Server; dbo_DimDate gets the data from the DimDate table in that database, and #”Filtered Rows” takes the table returned by dbo_DimDate and filters it so that you only get the rows here the DayNumberOfWeek column contains the value 1.

image

That’s really all there is to know about let expressions. It explains why you can do the kind of conditional branching that Avi Singh describes here; and also why, when I first tried to come up with a way to time how long a query takes to execute, I had to bend over backwards to ensure that all the variables in my let expression were executed in the correct order (though it turns out there’s an easier way of doing this). I hope you find this useful when writing your own M code.

Creating M Functions From Parameterised Queries In Power BI

Query parameters are, in my opinion, one of the most important features added to Power BI recently. The official blog post describing how to use them is great (read it if you haven’t done so already) but it misses out one other cool new feature that I only discovered by accident the other day: it’s now super-easy to create M functions from parameterised queries.

Why is this important? In Power BI (and indeed in Power Query), M functions are the key to combining data from multiple data sources that have the same structure. For example, if you have a folder of Excel workbooks and you want to read the data from Sheet1 in each of them to create a single table for loading into Power BI, functions are the key. Here are some blog posts with examples:

Matt Masson on iterating over multiple web pages:
http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

Ken Puls on combining data from multiple Excel workbooks:
http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

My M function showing a generic function for combining any data from Excel:
https://blog.crossjoin.co.uk/2014/11/20/combining-data-from-multiple-excel-workbooks-with-power-querythe-easy-way/

All of these examples involve writing M code manually. The big change in the latest version of Power BI Desktop is that you can do the same thing using just the UI.

Let’s take the classic example of combining data from multiple Excel workbooks and update it to show how things work now.

Say you have a folder containing three Excel workbooks containing sales data for January, February and March and you want to load data from all three into a single table into Power BI. The first thing to do is to create a new parameter in Power BI Desktop that returns the filename, including path, of one of the Excel files. Call it ExcelFilePath and configure it as shown here:

image

Next, you need to create a query that connects to the Excel file whose filename is used in the parameter and load the data you want from it. In this case let’s say you want to load the data from Sheet1:

image

This is all very straightforward; here’s the query that you’ll get:

image 

Unfortunately, at the time of writing, the Excel source doesn’t support using parameters when creating a new query, so you have to create a query and then edit it to use the filename that the parameter returns. You can do this by clicking on the gear icon next to the Source step in the query:

image

In the dialog that appears, click the icon next to the File Path property and choose Parameter, then from the dropdown menu choose the name of the parameter you created earlier:

image

Now here comes to the good bit. In the Queries pane on the left-hand side of the screen, right-click on the name of the query you just created and select Create Function:

image

This option allows you to take any parameterised query and create a function from it. When you do this, you’ll see a dialog asking for the name of the new function to be created (I’ve called my function GetSheet1) and allowing you to change the name of the parameters:

image

Here’s the original M code for the query with the parameterised Source step highlighted:

let
    Source = Excel.Workbook(File.Contents(ExcelFilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
    {{"Product", type text}, {"Month", type text}, 
    {"Units", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"

Here’s the M code for the new query created after Create Function has been selected:

let
    Source = (ExcelFilePath as text) => let
    Source = Excel.Workbook(File.Contents(ExcelFilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
    {{"Product", type text}, {"Month", type text}, 
    {"Units", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"
in
    Source

Where the original query points to the workbook whose path is returned by the ExcelFilePath query parameter, the new function takes a parameter (also called ExcelFilePath) to which any other query can pass any text value.

Now you have your function, the final step is to call the function on a table containing the names of all of the Excel files in your folder. Create a new query using the From Folder source:

image

…then point Power Query to the folder containing the names of all of the Excel files:

image

Remove all the columns in the table except Folder Path and Name, drag the Folder Path column before the Name column, then select both remaining columns, right-click and select Merge Columns to create a single column (called FullPath here) containing the full path of all the Excel files:

image

image

Next you need to click the Custom Column button and call the GetSheet1 function for the text in the FullPath column for each row in the table. Here’s the expression to use:

GetSheet1([FullPath])

image

Last of all, click on the Expand icon in the right-hand corner of the new column:

image

…and you have a table that contains all of the data from Sheet1 on all of the files in the folder:

image

Now for the bad news: queries that use functions like this can’t be refreshed after they have been published to PowerBI.com (see also this thread for more details). This could be why the functionality wasn’t publicised in the post on the Power BI blog. Hopefully this will change soon though…?

So, to sum up, it’s the early stages of an important and powerful new piece of functionality. In the past, a lot of the times when I found myself writing M code it was to create parameterised queries and functions; in the future I’m going to be writing a lot less M code, which is great news. I can’t wait to see how this develops over the next few months and I hope it turns up in Power Query too.

First Look at Pyramid’s On-Premises Power BI Integration

Attendees at SQLBits last week were given a sneak peak at the long-promised on-premises Power BI solution from Pyramid Analytics. Details are still scarce, but here’s a link to the five minute-long video that was shown:

https://pyramidanalytics.wistia.com/medias/jkyyn15yy5

Here’s a screenshot from the video showing a Power BI report, a Reporting Services report and native Pyramid content blended together into a single dashboard:

image

I have also managed to confirm one very important point: at least initially, Pyramid’s solution will only work for Power BI reports that use live connections to on-premises Analysis Services data sources (this is contrary to what I originally understood and what I said to a few people on Twitter last week – sorry). That said if you are using Power BI as a front-end to Analysis Services, and a lot of people are, this looks like it will be pretty cool.

The M Code Behind Power BI Parameters

For me the most exciting new feature in Power BI in a long while is the appearance of Query Parameters for data loading. We have been promised an official blog post explaining how they work (although they are very easy to use) and in fact Soheil Bakhshi has already two very good, detailed posts on them here and here. What I want to do in this post, however, is look at the M code that is generated for them and see how it works.

Consider the following parameter built in Power BI Desktop that has, as its possible values, the names of all of the days of the week:

image

The first thing to notice is that parameters are shown as a special type of query in the Queries Pane, but they are still a query:

image

This means that you can open up the Advanced Editor and look at the M code for the query. Here’s the code for the query shown above:

"Monday" 
meta 
[
IsParameterQuery=true, 
List={"Monday", "Tuesday", "Wednesday", 
"Thursday", "Friday", "Saturday", "Sunday"}, 
DefaultValue="Monday", 
Type="Text", 
IsParameterQueryRequired=true
]

From this you can see that the value returned by the parameter query is just a single piece of text – it’s the value “Monday” that is set as the Current Value, that’s to say the value returned by the parameter itself. The interesting stuff is all in the metadata record associated with the value. I blogged about metadata here, so you may want to read that post before going any further; it’s pretty clear that the fields in the metadata record correspond to the values set in the UI. All of the fields in the metadata record can be edited in the Advanced Editor if you want.

When the parameter is used in another query it is referenced like any other query value. For example, if you load the DimDate table from the Adventure Works DW sample database and use the parameter above to filter the EnglishDayNameOfWeek column then the code generated in the UI looks like this:

let
    Source = 
          Sql.Databases("localhost"),
    #"Adventure Works DW" = 
          Source{[Name="Adventure Works DW"]}[Data],
    dbo_DimDate = 
          #"Adventure Works DW"{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = 
         Table.SelectRows(dbo_DimDate, 
           each [EnglishDayNameOfWeek] = Day)
in
    #"Filtered Rows"

The filtering takes place in the #”Filtered Rows” step and you can see where the name of the parameter – Day – is used in the Table.SelectRows() function to filter the EnglishDayNameOfWeek column. This is nothing new in terms of the language itself because you have always been able to return values of any data type from a query, not just tables, and you have always been able to reference queries in other queries like this – in fact you can see me write the same kind of code manually in this video. What is new is that there is now a UI to do this and there’s no need to write any code.

Personally, I think the Power BI team have done a great job here in terms of usability and clearly a lot of thought has gone into this feature. It doesn’t do everything I would want yet though: the ability to bind the list of available values to the output of another query and the ability to select multiple parameter values at the same time are obvious missing features (and ones that would be needed to match the parameter functionality in SSRS). However I would not be surprised to see them appear in a future version of Power BI.

After seeing the code, I wondered whether I could edit the code in the parameter query to make it do more interesting things. For example, even if the UI doesn’t support data-driven lists of available values for a parameter, it looks as though it should be possible to replace the hard-coded list with a list of values returned by another query. Unfortunately this does not work: any changes I tried to the parameter query code were either ignored or removed completely. A bit of a disappointment but again, hopefully this will be possible in a future version.

Thoughts On SandDance And Power BI

After SandDance was announced at the Microsoft Data Insights Summit a few weeks ago I had a quick play with it, thought to myself that it looked like it it would provide a few more cool data visualisation options, and then almost forgot about it. More recently I spent some time looking at SandDance in more detail and it got me thinking some more about what its uses today are and what what its future might be. There has been a lot of hype surrounding SandDance but not a lot of clarity about where it is positioned in the Power BI story; to be honest I’m still not quite sure where it fits myself and I wouldn’t be surprised if Microsoft doesn’t know either, or at least is keeping its options open.

One thing that is worth pointing out is that it comes from Microsoft Research and is released through Microsoft Garage which is, and I quote, an “outlet for experimental projects”. This suggests that it isn’t a polished product but more of a work-in-progress or an experimental platform. This certainly matches my impressions of the tool and those of Ruth Pozuelo and Alon Brody, who have blogged about it already: in many respects it’s very sophisticated but in others it is quite limited. Will it ever become an ‘official’ product? Other tools have followed this path: you may remember Power Query was originally an experimental project called Data Explorer and released though a site called Azure Labs, a predecessor to the Microsoft Garage site, so it is possible.

Another aspect of the SandDance story that deserves discussion is whether it’s just another custom visualisation or something more. This post on the Power BI blog talks about is as though it’s the former and I guess you could see it just as a way of accessing a lot of new chart types (such as small multiples) for your reports. The charts its creates are certainly eye-catching, as are the animated transitions, and the importance of that  – especially for sales demos – should not be underestimated.

image

However, it seems clear to me that SandDance is really an interactive visual data exploration tool, and indeed this is what the SandDance website suggests:
”SandDance is a web-based application that enables you to more easily explore, identify, and communicate insights about data.”
Microsoft doesn’t currently have any other products that compete in this sector: Power BI reports and dashboards are for publishing pre-defined, semi-static insights rather than true ad-hoc analysis, and while Excel PivotTables are great for starting with a blank sheet and exploring your data, they are certainly not visual; I don’t think Excel PivotCharts are a true visual exploration tool either, more of a visual representation of data in a PivotTable. Does Microsoft need a product in this area? I think it does if it wants to compete directly with Tableau, the gold standard in visual data exploration. Adding SandDance to Power BI makes Power BI a much more rounded product.

A third question is this: why is there a standalone version of SandDance and a Power BI custom visual? This blog post contains an interesting statement from Steven Drucker, principal researcher on the SandDance team:
“Using the Microsoft Garage as the release platform gives us the freedom to run experiments with the more accessible standalone version, and as we learn what you like and what works, we can add the right parts to the Power BI visual,”
This strongly suggests that the standalone version is really just a place for testing new functionality and that the Power BI custom visual is the main focus. Does this contradict the point I made above, and is it just the standalone version that is the ‘experimental’ tool? I’m not sure, because at the moment there don’t seem to be many differences in functionality between the two versions. We’ll have to see how things develop. This statement also suggests that if SandDance does grow up to be a real product, it will be as part of Power BI. This makes commercial sense – every new Microsoft BI product should be integrated with Power BI in my opinion. What’s more, many of SandDance’s current limitations (for example around loading and refreshing data) are solved by using the capabilities of the Power BI platform.

However I’m not sure integrating SandDance into Power BI as a custom visualisation, or rather only as a custom visualisation, is a good idea. At the moment the SandDance custom visualisation feels a bit awkward to use: it’s one tool embedded inside another with two inconsistent and often overlapping UIs. I would prefer to see it as a separate tool launched from the PowerBI.com portal, similar to how the original Power View is/was launched from SharePoint, a third way to interact with data stored in Power BI alongside regular Power BI reports and Excel reports. Users should be able to launch it in the same way as Analyze in Excel and use it to explore a data set directly without having to create a report first, and if they find something interesting they should be able to pin what they have created as a visual to a dashboard, or save it for use in a regular Power BI report. Doing this would require a lot more time and effort on the part of Microsoft than just building a custom visual, but at the moment there seems to be no shortage of resources available to the Power BI team. SandDance is undoubtedly a great first step but with some more investment from Microsoft it could be a much more important part of the Power BI story.