How to: Group Dates by Week in Power BI

This post shows you how to use the date hierarchy and the grouping function to easily group your data by year, month, week in Power BI. This can be very handy when reporting against data that is timephased, such as sales transactions or Project Online schedule and capacity data.

If you need to start your week on a specific day of the week, you can create a new column based on your date, using this DAX statement:

Week Beginning Date = ‘TableName'[DateFieldName] – MOD(‘TableName'[DateFieldName]-1,7) + [Add 0 for Sunday, 1 for Monday, etc. to start week accordingly]

Creating Maintainable Power BI Report Models – Part 2

Power BI Kitchen

In this installment, the user facing aspects of your Power BI report models will be discussed. Much of this is considered fit and finish work. It is important that you take the time to address these issues as PowerBI.com enables users to build their own reports and dashboards from your data sets. This finish work will result in less time spent on support and making ad hoc updates.

Hiding data sets and fields in the Power BI Report View

When you are creating a complex report model, you may use transitional data sets or configuration type data sets to create your final data set. These transitional or configuration data is not intended to be used by the end user in creating a dashboard or report. Before releasing a report model, you should determine which of these data sets and fields to hide from the Report view. Doing so will reduce user confusion about this information, allowing them to focus on the data most important to them.

To hide a data set or field in the report view, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

Power BI

  • In the Field well, right click on a data set name or on a field
  • Select Hide in Report View (2)

image

  • The data set or field will appear grayed out (3)

image

 

Default formatting

Default formatting has a cumulative positive productivity effect for your users. Setting the default format for numbers and dates frees them from having to spend non-productive time “trying to get the data to look right.” It’s tedious work that can easily be eliminated with a few actions on your part. Your users will be happier and able to be faster at generating views.

Dates

In many data sources, dates are stored as datetime values. Project Online stores every date in this format and this makes sense from a transactional perspective. However, in the BI world, most users aren’t looking for time information. The time data creates visual noise instead.

Also, if you are creating content for international users, it is important to use the right formats such that Power BI uses the end user’s regional setting. This way your American and Australian counter parts both know that 3/11/2016 and 11/3/2016 are respectively, the same date. Date and Datetime formats that automatically adjust to the end user’s regional settings are marked with an asterisk *.

To set the default format for a date, do the following.

  • Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Data Type to Date. (4) Note, the format of the selected date column has changed.

image

  • Click Format dropdown, Date Time, select the first value, which has an asterisk (5)

image

Now, your date values will appear as follows.

image

Numbers

The same process applies to numbers as well. Many systems store numbers in a general decimal number format, leaving users staring at numbers with a variable number of decimals or cost data that isn’t apparent it’s related to money. Variable decimal places creates usability problems as it’s hard to scan the data. Not denoting costs with a currency symbol leaves the number open to misinterpretation.

Decimal Numbers

To format the decimal positions in a number, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Format to Decimal Number (4)

image

  • Set the number of decimals to show (5)

image

Costs

To format a number with a currency symbol, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Format to Fixed Decimal Number (4)

image

  • Set the number of decimals to show (5)

image

Categorization

The last set of good practices for report models is to categorize specific types of data such that the default behavior in Power BI is more user friendly. This applies currently to location based data and urls. By default, Power BI would treat this information as character data. If a user drags a city or country value to a dashboard, likely they will be interested in a map view rather than the name of the city or country. Categorization allows you to tell Power BI that the City or Country field is location data and that a map instead of a grid should be rendered by default. For URLs,, categorization tells Power BI whether to render the link as clickable or to render the image to which it points.

In all cases, click the column heading of the field to categorize and select the Data Category on the Modeling tab to set, similar to the actions above.

Location Data

You can categorize text data as location related or numeric data if it contains latitude or longitude values. The list of location categories is shown below.

image

Web URLs

If you want to make the URL clickable, change the Data Category to Web URL.

image

Image URLs

To render the image accessed by the URL, change the Data Category to Image URL

image

 

Following these simple techniques will result in easier to use and easier to maintain report models.

If you want to learn more, check out our Power BI sessions at http://academy.tumbleroad.com  Use code take100off to get $100 off of any paid Power BI class.

 

 

‘); // ]]>

‘); // ]]>

 

Creating Maintainable Power BI Report Models – Part 1

Power BI Kitchen

Professional chefs use a French kitchen organization approach called “Mise En Place”, which means literally, “putting in place.” It ensures that the placement of ingredients and utensils are optimally placed for efficient cooking. Power BI report models can require the same type of approach to ensure that your report models are maintainable over time. This applies whether you are doing reporting against Microsoft Project Online, Microsoft Project Server or Office 365.

We’ll focus on three techniques for the report developer’s back end configuration this week. We’ll cover the front end configuration next week as it relates to improving the end user experience.

Fast is not always good

Power BI allows you to accomplish a lot quickly when it comes to transforming data. However, if you aren’t smart about how you approach your back end configuration, you could be creating a maintenance nightmare for yourself.

Do you know what is what in a report model that you haven’t touched in months? These three techniques will enable you to easily understand and work with your transformation code over time.

What was I thinking when I did this?

This is a common problem for any developer who has ever done work in a rush. The reason for doing what you did in the fifteen transformation steps was blatantly clear the day you created the model. However, the sands of time have worn away the memories and now you are doing code archaeology to understand your previous work.

Power BI allows you to make your transformation steps self-documenting.

To make your transformation steps descriptive, do the following.

  • Open the query editor
  • Select a data set in the left pane. The list of transformation steps are shown in the Applied Steps pane on the right.
  • Right-click (#1) on transformation step
  • Rename the step with something descriptive

Power BI Renaming a Transformation

Instead of accepting the default “Grouped Rows” step name, you can rename the step to read “Grouping by Year, Week, Resource to provide weekly work totals.” Doing so creates a coherent story of what is happening and why.

Power BI Self Documenting Transformations

What is this data set again?

Power BI provides a default data set name, based typically on the source of the data retrieved. This may not be desirable as the data set may be an amalgamation of data from several sources and have a different intent than that indicated by the first data retrieved. Secondly, this name also appears to the end user and may have no meaning.

To make your data set name descriptive

  • Open the query editor
  • Select a data set in the left pane
  • On the right side, in the Properties Name box above the Applied Steps, rename the data set to be more meaningful to the user
    • If you are using our Conversation-centric design approach, you should already have some candidate names available
  • To update, simply type over the value in the Name field and click off of the field to save
  • When you save the model, the change will be saved

Below, we renamed AssignmentTimephasedDataSet to Resource Work Summary by Week.

Power BI Naming Data Sets

Where did I put that thing?

Another challenge that you may encounter is trying to find a query data set when you have several in a report model. Power BI allows you to create groups and assign your data sets and functions to a specific group.

If I am working on model that is in production, I can create a Version 2 group, copy the data set, move the copy to the Version 2 group. This way, I have both copies and can ensure I don’t accidentally change the wrong version.

In the example below, I used groups as a way of organizing my demos for a webinar. Each group demo number corresponded to a PowerPoint slide so it was easy to keep my place.

Power BI Using Groups

To create a Power BI query group

  • Right click anywhere on the Query Editor left pane
  • Select New Group…
  • Fill out the name and the description
  • Click OK

The description can be seen when you hover over the group name. It is very helpful for providing additional context.

To move a data set to a Power BI query group

  • Right click on the data set name
  • Select Move to Group
  • Select the Group

These three techniques will help you keep things organized and understandable. Next week, we’ll discuss best practices for enabling a great end user experience.

3 Ways to Rev Your Microsoft Project Online and Power BI Performance (Number 2 Is Our Favorite)

Are you having tired of waiting for long refresh times with Power BI? Perhaps, you are getting timeouts because you are retrieving too much data. There’s three easy ways to avoid these issues.

WHY IS MY POWER BI DATA REFRESH SO SLOW?

Many Power BI models refresh slowly as the models are not structured to take advantage of query folding.

Query folding is a process by which specific Power BI transformations are executed by the data source instead of Power BI. Allowing those actions to occur at the source means less data has to be sent to Power BI. Less data means faster data refresh times and smaller report models. Note, not all data sources support query folding, but oData for Project Online and SQL Server for Project Server do.

A sample of these foldable actions include

  • Column or row filtering
  • Group by and aggregation
  • Joins
  • Numeric calculations

For example, you need to find out the amount of work scheduled by week for the quarter. You are querying the time phased data from Project Online. If you aren’t careful, you may be retrieving hundreds of thousands of records. Query folding will make a huge difference in speed and the number of records retrieved. If you have the records filtered by Project Online before retrieving them, you may only receive a few thousand records instead.

ISSUE #1: NOT REFERENCING THE DATA SOURCE PROPERLY

This issue occurs primarily using oData sources, such as Project Online, Office 36 and other web based sources. Query folding breaks if you don’t properly reference the oData feed.

In order for query folding to work properly, the transformations that have folding support need to be the first things executed. If a non-folding transformation is added, no subsequent transformation will be folded. If you don’t reference the oData feed properly, the step to navigate to the oData feed isn’t foldable, therefore blocking all subsequent transformations from being folded.

ISSUE #2: DOING DYNAMIC DATE SELECTION IMPROPERLY

This issue causes more headaches with Project’s time phased data than any other action. In many cases, you are looking to create a range of dates for Power BI to query. If you use the Between filter in the Power BI Query Editor, there’s not option for Today’s date +/- a number of days. If you use many of the other Date/Time filters, like Is Latest, you still seem to pull back a lot of data.

Solving this particular issue requires getting familiar with the M query language so that you can understand how Power BI Desktop performs specific actions.

For example, let’s look at the Is Latest date selection. By default, your dataset is unsorted so Power BI creates a list of the values in the selected column and does a Max value. While this is correct, this could result in a lot of records being retrieved to perform a Max and get one record. The M code over a time phased table looks like this:

= Table.SelectRows(#”Filtered Rows”, let latest = List.Max(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

To get much better performance, make the following two changes. First, sort the dataset in a descending manner on the column which you are using to decide latest. In this example, that’s TimeByDay. Sorting is a foldable action so the data source will do the work.

Next, change List.Max to List.First. Since the latest date is the first record in the sorted dataset, a lot less data is required to get the answer. So, my statement is now = Table.SelectRows(#”Filtered Rows”, let latest = List.First(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

In testing, the original way required over a 1 Mb of data to be retrieved to answer the question. The new way only retrieves 127 Kb.

ISSUE #3: URLS ARE TOO LONG

This issue comes into play when working with Project Online and SharePoint based data sources. Project, in particular, has a lot of long field names. SharePoint has a limit of 2048 characters for a URL. If you are manually selecting a lot of field names, you can accidentally go past the 2048 character limit.

What this means is that Power BI receives a URL that can’t be acted upon. The default Power BI behavior is to then simply retrieve the feed without modifiers. If this happens on a source with many rows, this could significantly impact performance.

In this case, you would break up your dataset into two or more to fit within the limitation and then merge them back together in Power BI.

WANT TO KNOW MORE?

Join us for a free Power BI performance clinic on March 22! This session will do a deep dive into these issues as well as show you how to determine when folding is happening (and not.) Go here to register!

 

[imageframe lightbox=”no” lightbox_image=”” style_type=”none” hover_type=”none” bordercolor=”” bordersize=”0px” borderradius=”0″ stylecolor=”” align=”none” link=”http://academy.tumbleroad.com/courses/power-bi-performance-clinic” linktarget=”_blank” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/imageframe]