Category Archives: Power BI

Power BI Grouping

Grouping means combining multiple items into fewer items. It helps us consolidate and summarize things to understand them at a higher level of granularity. Let us see how to use Power BI Grouping done easily and quickly. This is useful for Ageing Analysis, Bin or Bucket Analysis, week / custom date range analysis.

Creating Power BI Grouping

Power BI provides a simple and consistent mechanism of performing grouping for numbers, text and dates. All that you need to do is Right Click on the field name and choose Create Group.

New Group menu option

Two types of grouping is possible. Bins based or List based.

List based Power BI Grouping

For text data we can only group based upon the List type. It basically allows you to select two or more items and group them into a single category. Ungrouped items are shown as Other category.  For example here, we have divided the Segments into Risky and Safe.

Grouping text - risky and safe segmentsimage

Of course, if a new segment appears in future data, it will go under Others. You will need to edit groupings to repair such issues. This type of grouping should be used only if there are few groups and the data (in this case, segments) do not change often.

If there are too many categories and they change often, it may make more sense to have a separate table containing two columns – the segment and the classification. This table can then have a relationship on the Segment column and provide a more flexible and easier to maintain method of grouping.

Group icon

The newly created Group is added as a separate field with a special icon – which looks like two overlapping boxes.

image

Bins based Power BI Grouping

For numeric values, bin based analysis is more common. For example, for payments overdue, we usually perform ageing analysis by 30,60,90, 120 days overdue. This can be done easily using Bin based Power BI Grouping

Bin size setting of  30 for ageing analysis

This gives us ageing analysis easily.

ageing analysis

Ad-hoc grouping of numbers by using the List method is also available if required, but it is not very useful if there are too many numeric values. If there are few numeric values, ad-hoc grouping will be useful. For example, if you have just 10 unique values like a score, we can use List based grouping to classify them into low, medium and high categories.

List grouping for numbers

However, if there are too many numeric values, it is better to create a separate table to categorize numbers.

Dates based Power BI Grouping

Dates based grouping is available by default in Power BI. When you add a date type of column to a visualization, it automatically becomes broken down into Year, Quarter, Month and Days.

Bins can be created by specifying the size (as we saw with numbers)

image

This is very useful if you want weekly groups. By default, Power BI does not give you weekly breakdown. But if you group by days and choose bin size of 7, you get weekly grouping.

Number of bins

By default we create bins by specifying its bin or bucket size. But we can also use another method – by specifying the number of bins. This is very simple. The actual min and max of the column is found out and the range is divided into specified number of bins.

image

Custom bins require separate tables

Any custom bins require a separate approach.

For example, we want ageing from 0 to 30 and 31 to 45 then 46 to 90… this type of custom bin analysis requires you to create a separate table and then do the math by add a calculated column.

Similarly, for custom date grouping like custom weeks, calendar vs fiscal years and so on, we need a custom date table.

How to conduct a Power BI Pilot

Power BI is becoming popular. Therefore, many companies are interested in considering a Power BI Pilot project as a proof of concept. While interacting with customers, I have noticed that many such pilots fail. The failure is NOT due to the capabilities of the product, but due to other factors which are controllable. In this article, I have listed a process which prevents common errors and improves the credibility of the outcome.

Power BI Pilot - the process Continue reading How to conduct a Power BI Pilot

Split column into rows

A brilliant new feature is now available in Power BI – Split column into rows. To understand why we need it, you must go and read the article  – Analyzing badly captured Survey data or feedback forms. This method used Power Query concepts of Split and Unpivot. Now these have been combined into a single, intelligent command called Split columns into rows.  It sounds confusing at first. But soon you will realize that it is an amazing tool.  Learn it just 4 minutes.

Raw data looks like this

Split column into rows - raw data

And you get a report like this. No need to use formulas or do any manual work.

Split column into rows - Final Charts

You must have the May 2017 update for Power BI Desktop installed.

Continue reading Split column into rows

Power BI Dates before 1900

This is a short post. It is like an FYI mail. Excel never understood any dates before 1900. We got used to that limitation over the decades. But Power BI does understand Dates before 1900. The best part is, you do not have to take any specific action. It just works.

Here is the raw data and the Power BI output.

Dates before 1900 - raw dataDates before 1900 - in a chart

If you try this in Excel, it just will not work. Now that you know this, starting using Power BI with Dates before 1900.

Mind you, the Power BI documentation says that the earliest limit is 1900. It still works for dates before 1900. Drill down is also supported. Here is the same data at Day level.

Drill Down

This ability  may make historians and archeologists partially happy. There time scales are huge and Power BI does not support that much of a range. But still, it is an improvement worth knowing about.

CIO Crown Session Video: Disruption, Transformation and Acceleration using Office

This keynote was delivered at the CIO Crown event in Mumbai (India), August 2016.  200+ CIOs attended the session. It was an honor to showcase the power of Office365 to a highly motivated and intelligent audience… love it!

View on YouTube

View it in 720p resolution in full screen for maximum impact.

Continue reading CIO Crown Session Video: Disruption, Transformation and Acceleration using Office