Tag Archives: Dashboard

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

Understanding Excel Dashboards – the practical way.

Current fashion is about Big Data and Analytics. One related term is very popular – Excel Dashboards. Let us start understand the concept first and then explore the  benefits and methods of creating useful Dashboards.

Data visualization with Excel Dashboards

Estimated reading time 10 min

Continue reading Understanding Excel Dashboards – the practical way.

Using color to simplify interpretation and enhance analysis

Older version of Excel could display only 52 colors. New version (2007 onwards) can display 16 million colors. Yes, that sounds like an overkill, but Excel uses this extra capability in a brilliant way. It provides us with a very easy and useful method of using color to understand information more easily. Read on to find out how…

Use colors for easier analysis in Excel - Dr. Nitin Paranjape Continue reading Using color to simplify interpretation and enhance analysis

Working with Excel data? Learn Power BI

The need

Office is a funny product. Everyone thinks they know it. What is the definition of “Knowing” Office? = “I know how to get my work done”

Unfortunately, even if you get the work done, it may not be in the most efficient way. Data handling in Excel is probably the single most important drain on human effort everyday.

Billions of people who have the false sense of knowledge use the same old, bad, slow, contorted methods of working with text files, exported reports and spend their precious life cleaning up data, removing unwanted headers, unpivoting data manually, copying and pasting… it is a global pity.

The primary problem? Once you found ONE method which works, you never attempted to find out if there is another, better method available.

Over time Microsoft has been adding many new ways to simplify things and eliminate manual work.

But now, there is a revolution happening. Power BI tools from Microsoft.

All that I am talking about here requires Excel 2013 Professional edition. But don’t worry. You can actually sign up for a trial version for free. See below.

Why do we need it?

The way we work with data has not changed at the core.

image

Ideally we should be spending maximum time in the analyze phase – because that is where we learn from what happened. That knowledge will help us in improving the future.

Unfortunately, we spend too much time in getting data, cleaning it up and summarizing it.

Now Microsoft has added a set of tools to Excel to simplify and automate many of these useless tasks.

Getting data from various sources is now done with Power Query. It also helps you in cleaning up the data, merging / splitting, adding calculated columns, creating relationships and so on. Data can be retrieved (and periodically refreshed) from a variety of sources.

Power BI - Power Query

Don’t worry. The good old ODBC / OLEDB, Text and Excel also works!

Excel 2013 also adds a great feature called Flash Fill to help us clean data instantly – without formulas or manual work. Although this is not a part of PowerQuery, it is a very powerful feature. I would have named it Power Data Cleanser Smile

Once data is available and clean, it is NOT stored in Excel sheets – because there is a limit on number of rows. Furthermore, large data in Excel inflates the file size and makes it excruciatingly slow to work on it.

Therefore, the clean data is stored in a new powerful friend – PowerPivot. It can store millions of rows of data and still have very small file size.

SNAGHTMLc9e9125

Now what we have is called a Data Model. It is time to summarize data. This can be done in four ways.

  1. Traditional Pivot Table and Pivot Chart
  2. PowerPivot Pivot Table and Pivot Chart
  3. Power View
  4. Power Map

Traditional pivot table can still be created with this data and used to summarize the data. We are already familiar with it so no re-learning required. You still benefit from the ability to created relationships (eliminating the VLOOKUP trap) and millions of rows of data handling.

PowerPivot also creates Pivot Tables in Excel. These behave like the good old Pivot Tables but these provide new functions and features which are simply not available in traditional Excel. Many complex calculations which would otherwise require you to approach IT to create a data warehouse (Cube using a BI Tool) can now be done with these powerful Excel formulas. Technical name for these new formulas is DAX.

image

Power View is an entirely new way of summarizing and visualizing data. This gives you s blank page to start with. You just drag drop the fields you want and choose how they are represented (tabular, chart, map, etc). In the process you create a dashboard quickly. Every visual element acts like a filter giving you unimaginable level of analytical capability.

image

Power View also supports maps. Using Bing Maps, your data can be shown on a map for better understanding. This works right within Excel – no special software required.

Power Map is a more evolved version of mapping functionality – a free add-in for Excel 2013. This allows multiple layers, different visualizations and even creation of an animated step by step video to show your plan of action and to present data in a serial manner.

image

Ask queries in English language

As though all this was not enough, you have a miracle feature. You can actually type a query in English, like “Show sales qty for each region as column chart” and it actually does the job. You have to see it to believe it.

If you have been in IT for long enough, you will remember that there was English Query in earlier versions of SQL Server. Later Microsoft dropped the feature. Now it is back with a bang. It works even with simple Excel data with no vocabulary or synonym configuration!

Reporting and Dashboards

Creating reports is one thing. Next step is to share it with others. This is best done using SharePoint. It offers a special app for PowerBI reports. Reports can be seen and interacted with on any browser (HTML 5 version coming soon. Currently requires Silverlight) and any device or tablet (Microsoft, Android, iPAD, iPhone).

image

Try it yourself

GO to the Power BI Site and sign up for a trial subscription. It gives you everything you need… Full version of Office 2013, SharePoint and PowerBI app for SharePoint (full Office 365).

Worried that your existing Office installation of older version will be affected by the new version? No worries. Even that has changed now. The installation of Office 2013 can work in parallel with older versions of Office. Both work independently without affecting each other. This is called appV (application virtualization). The deployment method is called Click To Run.

We will cover more of Power BI in upcoming articles.

Update (Aug 2016): I’ve just released my first Power BI MOOC on Udemy. See details here.  Use discount code AUTUMN40 to get the course at 20% discount.