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.

Three Time Zones in Outlook

Simple but useful stuff. Now, Outlook supports Three Time Zones in the Calendar. To set the time zones, right click on the time display in Calendar and choose Change Time Zone… or go to File – Options – Calendar – Time Zones.  If you work with customers or your own teams globally, this can be a very useful feature.

Set three time zones

How to select Three Time Zones in Outlook

One time zone is always shown by default – that is the current time zone – as per Windows regional settings. You can choose two more time zones. Remember to specify labels which are short and clear. If you use wrong labels, you will end up confusing yourself.

Three Time Zones were added in May 2018. Since many years, Outlook supported only two time zones.

The time zones are visible in the Day, Week and Work Week view. In the month view, there is no place to show the time portion.

Time showing Australia, Hong Kong and India time

Suppose, I have three time zones set – India, Hong Kong and Australia. What to do when I am actually visiting HK? Simple. Click the Swap Time Zones button. This cycles the time zones. Choose the first time zone to be the place where you are. That’s it.
Remember to reset it to your home time zone when you return to your base location.

Choose the desired time slot display

By default, Outlook shows one-hour time slots. Depending upon your work profile, you can make it more detailed. You can go down to 5 minute granularity. This is useful if you are using Calendar for managing lots of related activities like organizing an event calendar or a training program schedule.

Change time scale menu

There is a special 6 minute time slot display – for legal and other consultants who are billed by the hour. For them, dividing the hour into 10 slots of 6 minutes each is easier for calculating their charges rather than dividing it into 12 slots of 5 minutes each!

Arrange a meeting based upon customer convenience

In another related feature, while creating a new appointment, you can see other time zones. Click the Time Zone button in the New Appointment window.

Select the customer time zone and schedule the appointment as per their convenience. When you save the Appointment, it is translated automatically into your base time zone.

Unfortunately, WHILE you are editing the appointment, you can only see the time in the target time zone. But it is still better than manually calculating time zone offsets.

Power BI Workshop

Saturday, 26 May, 9 am to 5 pm, Mumbai

Waterstones Hotel, behind The Lalit Hotel, near International Airport, Mumbai
INR 20,000 + taxes per participant

Limited seats. Register Here

Amazing Value
  1. Full, 6-hour video recording of the entire session which can be used to train more people internally
  2. Email support for 2 months
  3. Practice Exercises with sample files
  4. Online, 2-hour follow up session
  5. Work with your own data

You must bring your own laptop. Additional software needs to be installed BEFORE arriving for the event.

Power BI Workshop Content
  1. Disadvantages / Pitfalls of data analysis in traditional Excel
  2. Understanding the Data Model
  3. Good vs Bad data
  4. Good data checklist
  5. Importing and cleaning up data using Power Query
  6. Get and Transform features
  7. Web data import and live refresh
  8. Combining data from files in a folder
  9. Combining data from multiple sheets
  10. Eliminating the use of VLOOKUP
  11. Large data handling using Power Pivot
  12. Creating dashboards using Power View
  13. Power BI Desktop usage scenarios
  14. Power BI portal
  15. Choosing the right visualization
  16. 3D maps using Power Map
  17. Sharing reports
  18. English language Q&A
  19. Bookmarks and storytelling
  20. Custom visuals
Who should Attend?
  • Anyone who spends lot of time with Excel creating reports, charts and analyzing data.
  • If you use any other BI tools like Tableu, Qlik, BO, Cognos, etc.
  • If you work extensively with Pivot Tables
Who should NOT attend?

If you want to learn DAX language, specifically, you should NOT attend this workshop.
This workshop is designed for people who have no exposure to Power BI in the past. The objective is to migrate them from the painful manual processes and legacy charts to the modern process of data visualization and interactive analysis using Power BI.

Learn from the Guru

Dr Nitin Photo for Power BI Workshop

Dr. Nitin Paranjape has coached over 330,000+ professionals across 18 countries. He is recognized as the Most Valuable Professional by Microsoft, for 15 years. Dr. Nitin is a coveted speaker with a unique humorous and motivating style of teaching. Detailed profile

Dr. Nitin’s work on Analytics

50+ Data Analytics  Blog articles
Power BI video for beginners
Shadow Analytics video for CIOs
Data Analytics Webinar
Power BI for developers video

You will be able to …
  1. Create your own interactive dashboards using Power BI
  2. Understand data quality problems and clean up data efficiently
  3. Combine data from multiple sources and create an optimal data model
  4. Choose the right visualizations to represent data in a meaningful way
  5. Design interactions within the data to simplify and amplify the usefulness of data
Venue

Waterstone Hotel
Behind The Lalit, Near International Airport, Mumbai.
Valet Parking Available

Power BI Workshop is subject to cancellation due to inadequate number of participants or factors beyond our control.

In case of cancellation, full refund will be provided.

Is Power BI Free ?

This is a commonly asked question. I will try to answer it in the simplest possible manner. Of course, this is as of May 2018. Things change very fast. So please check online for the latest status. Power BI Free does exist. In two forms. One is built into Excel and one is a subscription option.

Power BI Free in Excel

Let us understand what Power BI itself is. In simple terms it is a new way  (now 8 years old) of handling and analyzing data. It was created to overcome limitations of Excel and provide modern ways of analyzing information – visually as well as statistically.

Power BI was originally created as a set of add-ins to Excel. Even today, these continue to be add-ins. Power BI is more of a brand name in the context of Excel. You will NOT see a menu called Power BI. But it consists of the following components:

  1. Power Query – which is a method of importing and cleaning up data from 70+ sources. This tool revolutionizes the way in which we managed raw data. All the manual and tedious work, weird custom macros … all gone! What’s more, it allows you to refresh data by repeating the import and clean-up steps automatically. Which means, second time onwards, the effort is zero.  Power Query comes in two forms:
    1. Manually installed Add-in – for Excel 2010 and 2013.
      Here the add-in creates a separate tab (Menu) called Power Query. It has all the query handling options.
    2. Excel 2016 onwards, this add-in is pre-installed. The separate menu is removed. It now lives in the Data tab as a group called Get and Transform.
  2. Power Pivot this has two components
    1. Data Model – a database built-in to Excel which can handle millions of rows of data and multiple tables. It can be used to import data from multiple sources. Data Model compresses data and stores it inside the same XLSX file. It works much faster than having the data in Excel sheets (on the same PC).
    2. Power Pivot Table – which works on the data model (instead of data in Excel sheets). Creating a Pivot Table from data model allows us to use multiple tables of data and create relationships between them.
  3. Power View – is an add-in available since 2013. This is used to create interactive and visual dashboards using the data model. It is extremely flexible and powerful. It provides bar / column charts, matrix (like pivot table), pie charts, scatter charts and map (geographical visualization).
    This tool requires an outdated component called Silverlight to be installed on the PC. As of now, most PCs do have this component installed. But Microsoft itself has discontinued this component and it is not supported. Due to this, Power View becomes a limitation of sorts.
    What’s more, to view and interact with the dashboards on a browser, we need the same Silverlight component. Unfortunately, this works only on Internet Explorer – which is almost phased out globally.
    Due to these limitations, Microsoft is discouraging the usage of Power View. As a manifestation of this issue, the Power View button in 2016 is hidden. We have to customize the ribbon to add the Power View button.
    Although Power View is working as of now, it may be discontinued in future. Many organizations are disabling the use of Silverlight due to security risks associated with it.

Sharing Excel Power BI reports

Sharing reports created in Excel is done in two ways:

  1. Send the file by mail – which can lead to issues because we are making multiple copies of the file. If files exceed the email size limit, this option is not available. Unfortunately, we have already found the workaround for this limitation. We just copy paste the visualizations in PowerPoint and send the reports. This is the same as what we have been doing for decades: Copy paste Pivots and charts from Excel to PowerPoint.
    Although this works, it defeats the purpose because all the interactivity and flexibility of analytics is completely lost.
  2. We can store the Excel files containing Power Pivots and Power View on OneDrive and use Internet Explorer to view and interact with the reports. As discussed earlier, it requires Silverlight and is a risky option.

The REAL Power BI Free

Although Power BI started as individual component Add-ins, Microsoft wanted to provide a more cohesive and safe solution. That is why Microsoft combined the above components into a separate tool and created Power BI Desktop.

Power BI Desktop is a stand-alone application. It does not need Excel to be installed. It is a combination of functions provided by Power Query, Power Pivot and Power View.

It can connect to 70+ data sources (as of May 2018) including Excel files, CSV, TXT and databases. Power BI Desktop can be used for free by anyone after registering on the Power BI web site.

Using this Power BI is simple. Download the Power BI Desktop tool. Create reports on the PC and publish them on the Power BI Portal (web site). Thus far it is free.

Sharing it with others requires the paid version. Detailed comparison can be seen here.

Sharing the PBIX file

Power BI Desktop is used to create reports and the Power BI Portal (website) is used to publish and share reports. Sharing requires a paid (PRO) subscription.

However, smart users will realize that it is still possible to share the Power BI Reports by either mailing the PBIX files or storing them on some cloud platform.

While this is technically possible, it suffers from the same disadvantages which Excel based sharing suffers from. If the data is large, the file size exceeds email limit. Even if you save the file on cloud and share the link, the other users have to download the file before opening and viewing it. Furthermore, they can edit everything in the file including the report visualizations, filters and interactions.

This basically means, the original report is no longer the original report – leading to multiple versions and confusion.

Feel free to post your queries here.

Power BI Free Logo

Transforming Shadow Analytics

This content is relevant only if you are a CIO (or IT decision maker). Here is the video of the session I conducted at CIO Power List event on 4th May, 2018, at Conrad, Pune. Shadow Analytics has been around ever since “shadows” – also called end users – are around. Everyone knows about. Some people tried to eliminate it. Nobody succeeded.

This 30 minute video explains how to use Shadow Analytics as an opportunity to empower rather than restrict users and improve effective utilization of data.

Demos Included

The demos included in this Shadow Analytics video are:
Flash Fill, Insights, Explain the increase and Q&A.

Insights option in Insert menu of Excel

What is Shadow Analytics?

It is all kinds of data capture, clean-up, manipulation and report generation performed by end users without IT intervention.

If you generate a report from a business system (which is built or managed by IT), it is alright. But if you copy paste data from multiple such reports into Excel and then generate a new report, it becomes “Shadow Analytics”.

As you can imagine, it is difficult to eliminate it. Irrespective of how much time and effort you have spent on creating the most flexible ad-hoc reporting systems, it is impossible to provide every possible variation that users want. Therefore, Shadow Analytics has always been there and is likely to survive in the foreseeable future.

Problems associated with Shadow Analytics

Primarily two problems. It is extremely error prone and time consuming. There are lots of related problems. The root cause is that data is handled in a casual manner without regard for its recency and in a completely undocumented manner.

This can lead to wrong decisions, delayed decisions, increased operational risk and enormous wastage of precious time.

Summary

It is impossible to handle and correct the data sources and deliver data to users in a manner which is so easy that they stop doing the manual capture and clean-up altogether.

Once clean, accurate and updated data is available as input, creating reports can be done by end users in a more informed and productive manner.

Left hug Computer Right hug

Events from email in Outlook

This is a very convenient feature available in Outlook. You may be using it even without knowing about it. Outlook can analyze your incoming emails and create calendar entries (events) based upon them. Four categories are understood as of now, flight bookings, hotel reservations, car bookings and package delivery.

This is based upon machine learning algorithms – no human is looking at your emails. Once a mail is detected as an event, a calendar entry is created automatically. Here is an example of a travel booking.

image

This mail was automatically processed by Outlook and the following calendar event was created:

SNAGHTML533814b  image 

Check-in reminder

In addition, it created a reminder 3 hours before the departure time. That’s nice but there is more. Exactly 24 hours before the flight departure, I received an email reminder for checking-in. This reminder did not originate in my calendar. It came from a generic reminder service.

SNAGHTML5374b92

Customize automatic processing

To change these settings, go to Outlook web access – Options (the gears wheel) – Automatic processing. Notice that the Package Delivery option is not selected by default.

image

Although it sounds fairly simple, the internal working of this feature is quite complex. Why? Because there are thousands of different formats for hotel and flight booking mails. It has to understand the relevant fields and create the calendar events. This is where machine learning is helping us – behind the scenes.

Airplane

PowerPoint icons to shapes

PowerPoint provides 500+ icons in SVG format. I recently published a blog which has a downloadable version of all icons in a PPTX file. Scalable vector graphic format icons cannot be edited inside PowerPoint. You cannot treat them like any other PowerPoint shape. But now, we can convert icons into shapes. Once converted to shapes all the PowerPoint shape features available. You can also use the Merge Shapes option to combine or subtract shapes. Here is how you do it:

insert a new shape,  resize it as required , right click on it  and choose Convert To Shapes. 

image

Read the dialogue which appears and click  Ok.

2018-03-12_11-28-55

The icon may get converted to one or more shapes. If it has more than one shape, it will be in a grouped state. So it is a good idea to right click on it and try Ungroup. If Ungroup option is inactive, there is only one shape.

image

Here we have three shapes. Now the shapes can be used like any other PowerPoint shape.

What to do with the shapes?

Once Icons are converted to shapes, there are few things which can really add value. Color and animation. I will write separate blog for this but here is an example.

The icon above is from the Analytics category. It is a speedometer icon which is often used in performance management. Management dashboards showing KPIs often use this type of speedometer graphic. Here is how we can enhance the speedometer icon after it is converted to shapes.

image

With Animation, the needle can be made to move to show the current state of performance.

More stuff on icons coming soon…

What is Microsoft Teams

May be you already know this. But still, read for just 2 minutes more to clarify your thoughts.  Teams is an app which streamlines teamwork. That is the obvious definition. But this is an incomplete description.

Coordinating with multiple persons is Teamwork. Agreed. But there are a plethora of apps available to collaborate with others. What is special about Teams?

Teams is an app which helps you work with all types of Technologies. So it is not just teamwork with other people, it is with multiple technologies as well.

Continue reading What is Microsoft Teams

Microsoft Teams: Knowledge Pack

Here is a list of articles I have written so far about effective usage of Microsoft Teams.
If you would like me to cover some missing topic or a specific area of your interest, please post it as a comment. This is a live book. As I add more articles about Teams, this list will be updated.

image

  1. What is Microsoft Teams
  2. When to use Microsoft teams
  3. When NOT to use Teams
  4. Notice the notifications
  5. How to reply in Teams – Common Mistake
  6. One person Team – how to use Teams alone!