Skip To Main Content
business woman using tablet while facing a presentation screen in meeting room
Tools

Data visualization with Power BI: the new frontier

This Microsoft Office 365 product is one of a number of data visualization tools that can collect, transform and share important data in an easily understandable way

business woman using tablet while facing a presentation screen in meeting roomWhile both Excel and Power BI can be used to collect, transform and model data, Power BI adds the ability to present and share any kind of data through rich interactive visuals in reports and dashboards (Getty Images/Hero Images)

As your organization gains access to ever-growing volumes of data, information in and of itself is the most important asset you own. “Data is the currency we trade in,” says Ken Puls, FCPA, FCMA, president and CTO of Excelguru Consulting Inc.

But as data gains in importance, presenting it in a way that people will understand becomes key. And that’s where visualization tools come in. These relatively new products have the power and punch to gather information from disparate sources and present it in a graphical way. Although there are several products on the market, Power BI is among the most popular.

WHAT IS POWER BI?

First released in 2015 as a standalone product, Power BI is part of the Microsoft’s Office 365 suite of products, which also includes Excel. 

While both Excel and Power BI can be used to collect, transform and model data, Power BI adds the ability to present and share any kind of data through rich interactive visuals in reports and dashboards. For example, Puls says a marketing company might have access to a lot of information on how many people visited its website, and of those, how many clicked on the information form, and of those, how many booked an appointment. So it might use that information to determine its sales lead funnel, its percentage capture rate and cost per lead. 

There are also more unusual examples. As Sophie Marchand, CPA, a consultant who specializes in training, explains, she knows of a surfer who was able to use Power BI and Power Query to show how fast was he was going with the various fins he was using on his surfboard. She also knows of a diabetic man who uses a device to measure his blood sugar and heart rate, then merges the data on a calendar view with his Outlook calendar and emails. “Using Power BI, he was able to see that his blood sugar goes down and his heart rate goes up whenever he gets an email from his boss,” she says. 

As Puls explains, this kind of visualization was not possible 10 years ago. “But now, if you can dream it, and if you have access to the data, you can start to model,” he says. “It’s amazing what you can do with data today.” 

HOW DOES POWER BI WORK?

Here is how you would produce and share a report or dashboard on Power BI Desktop. 

  1. Using a tool called Power Query, you collect your data from various sources, clean it and reshape it into a tabular format so that it can move to the second stage. As Puls explains, you can actually create several tables here. 
  2. Here, you load the data from step 1 into the Data Model (also known as Power Pivot in Excel). At this point, you can link tables together based on relationships in order to create a dimensional model. (As Data Warehouses explains, dimensional models are made up of “fact” and “dimension” tables. A “fact” is a numeric value—such as sales—that a business wishes to count or sum. Dimensions are things of interest to the business, such as customers, channels and products. You would link any or all of the dimensions tables to the fact table.) 
  3. When all your dimensional models have been set up, you can move into visualization. Here, you write some measures (formulas) and drop them onto a variety of different visuals such as tables, charts and maps. All of these are interactive and include drilldown and other features. 
  4. You publish your report to what is called the Power BI service. This is a secure portal that allows you to control whether people can share the report and whether they can access the underlying data. You can also update the report every month (or whenever you want) by re-publishing the file manually, or you can even schedule an automatic refresh in many cases.   

HOW DOES EXCEL CONNECT TO POWER BI?

Much of the technology that is in Power BI also exists in Excel. As Puls points out, “You’ll see a lot of people jump on Power BI and say, ‘I need to move everything off Excel and put it all in power BI.’ But they don’t realize that Power Query, Power Pivot and the ability to create pivot tables and pivot charts are built into Excel versions from 2016 onward (even earlier for Power Pivot). That means you have the option of using Excel right up until the point where you want to publish and possibly share your report.”

Here is how you might combine Excel and Power BI to build a financial statement, starting with a general ledger listing that has been exported to a text file.

  1. Using Power Query’s user interface driven features, you import various data sources (actuals from a database, a chart of accounts from a text file, and your budget from a different Excel file), clean them up and reshape them into proper fact and dimension tables. These tables are then loaded to Power Pivot.
  2. In Power Pivot, you relate the tables, linking them together to create a dimensional model—just as you would do in Power BI.
  3. You then build three formulas to create the entire financial statement using a pivot table that shows actual, budget and variance.
  4. Once you are happy with the result, you publish it to Power BI, where you can add other visuals if desired. And as explained above, you can update the statement next month with a simple click. 

IS TRAINING NECESSARY?

Although some users assume they can learn Power BI in a day, both Puls and Marchand stress that this is absolutely not true. 

As Marchand points out, Excel users need to acquire new competencies to use Power BI, such as data cleansing, data normalization, data modeling and data visualization. After a day of training, they will see only the tip of the iceberg. “Power BI basically lets the user do everything that was done before by the IT team/business analysts,” she says. “But they went to school a very long time before they were able to learn those skills. Can you imagine an IT specialist saying they were able to learn accounting in a day just because they had a fancy new piece of software?”

Puls agrees: “No one would go into a surgeon’s office and bust out all their tools to do open heart surgery without some training. I think you should approach your financials with the same respect.”   

POWERING UP YOUR BI

Want to learn more about building dashboards and reports with Microsoft Power BI? In CPA Canada’s online course, Data Preparation: Introduction to Power BI, you can learn the key concepts you need to create data visualizations. And in Data preparation: cleansing, modelling and dashboards, you’ll find out how to properly prepare your data in Excel using Power Query, which is the stepping-stone to effective use of power BI. Both courses are part of CPA Canada’s data management certificate.

WHAT DOES POWER BI COST?
Any single user can use Power BI Desktop for free. That includes data cleaning and preparation, custom visualizations and the ability to publish to the Power BI service.

But as Puls explains, once you want to share your Power BI report with someone else, you need to have a Pro plan, which costs about $13 (US$9.99) per user per month. Since both the author and the recipient need to have a plan, the cost is about $26 a month. And as you add more people, the cost can add up. However, once you arrive at a certain threshold, you can choose a Premium plan, which lowers the average for each user.

The key, however, is that you can get started for nothing. As Puls puts it, “can run a trial and when you get into sharing, you can test whether the product makes sense before you go all in. Many companies choose that route.”