Tag Archives: Data Visualization

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

How to create Custom Data Labels in Excel Charts

I received a query from a customer about dynamic data labels for charts. Instead of replying directly, I thought of writing this article. This will help all of you in refining your charts. The idea is to create a chart which explains the fluctuations using text based explanations. The best part is, the explanation can be a part of the data itself.
Watch the two minute video and read details below. This works with Excel 2013 onwards. I have also included the solution for older versions, which is not as elegant, but it works.

Continue reading How to create Custom Data Labels in Excel Charts

Power BI Desktop course – Need your inputs

Power BI is powerful, as the name suggests. But it is also confusing. Exactly what what does Power BI mean? There is no simple, single line answer. Let me try. It is a set of new tools to analyze old data in new ways. Today the simplest way to get started it to sign up for a free Power BI subscription and use Power BI Desktop tool to create your reports and dashboards. I have already written an introductory article and published a walkthrough video.

Power BI Desktop course on Udemy

I feel this is one tool which can transform the way we work on data and analyze it. The primary reason is that it is independent of Excel. So all the bad habits which we have developed in Excel over decades simply disappear! I am planning to create a course on Udemy for Power BI Desktop. I would like your inputs on the content and any additional ideas or wish list. Read on to have a look and post your comments. You can also send a mail to me on nitin@maxoffice.biz

Continue reading Power BI Desktop course – Need your inputs

In-depth: Excel Recommended Charts

Now that we know about Excel Recommended Charts, let us explore it in-depth. This is an implementation of artificial intelligence or machine learning at your fingertips. Don’t underestimate it… exploit it.

Excel recommended charts

Photo credit: Elsie esq. / Foter / CC BY and salendron / Foter / CC BY-NC-SA

Continue reading In-depth: Excel Recommended Charts

Don’t just make charts the way boss wants. Use Recommended Charts

There are two types of charts. First type is a chart which you create to interpret data more effectively. The second type is more common. These charts are blindly created billions of times everyday – why? Because boss wants it that way!

In this article, I am going to attempt to change the mindset of all users of Charts. Please spend 5 minutes of your time reading this article and trying out a new thought process. I am confident that it will add value to your life.

Excel Recommended Charts

Photo credit: Elsie esq. / Foter / CC BY and salendron / Foter / CC BY-NC-SA

Continue reading Don’t just make charts the way boss wants. Use Recommended Charts

New, better alternative to Pie Charts: Treemap

We have been using Pie Charts for decades. Although these are useful for looking at proportions, there are disadvantages. Now, we have a more effective and easier to interpret chart available in Office 2016 – Treemap. What’s more, this chart is available for all devices – including mobile phones!

TreeMap - better than Pie Charts

Continue reading New, better alternative to Pie Charts: Treemap

Caution: Visual Interpretation Error in Data Analytics

We have seen how to put data bars in Excel (Bar charts with a difference). These help us compare any value with any other value visually. This helps us understand patterns in the data.

example for Visual Interpretation Error

If not used properly, this great feature can lead to confusion and misinterpretation as well. Here are common scenarios and the solutions.

Continue reading Caution: Visual Interpretation Error in Data Analytics