Working with Excel data? Learn Power BI

Data handling in Excel is probably the single most important drain on human effort every day. That is where Power BI comes in.

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 Power BI?

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

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.

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

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.

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.

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.

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.

Ask queries in English language

As though all this was not enough, you have a miracle feature. You can type a query in English, like “Show sales qty for each region as column chart” and it does the job. You must see it to believe it. Power BI Q&A is fully customizable with custom vocabulary.

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!

Power BI 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).

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.