Flexing the muscles of Power BI

After introducing Power BI last month, it’s time to tap into a couple of the most powerful aspects of Microsoft’s business intelligence software.

As you might recall, Power BI provides a number of cool features:

Extraction: Collect data from a range of different sources, eg, Excel files, CSV files, Access/SQL databases, many proprietary databases with Open Database Connectivity (ODBC) connections, online webpages, and so on.

Transformation: Clean and transform data by removing rows, sorting, filtering, and merging with other datasets. Many repetitive and cumbersome tasks may be automated using this tool.

Avoidance of VLOOKUPs/unnecessarily large data tables: Link multiple smaller different data sources together to perform analysis across multiple dimensions in an intuitive manner, rather than relying on one “wide” data table with many columns (fields).

Analysis through visualisations: Create charts and custom visuals using a point-and-click user interface, as shown in the screenshot below.

Collaboration: Share reports and dashboards likes the ones shown below with others in your organisation.

Underlying programming languages

In Power BI Desktop, a dataset is defined by a single query that specifies what data to include and how to transform it. The query is made up of related steps that build upon one another to produce the final dataset, which can be used to create visualisations for reports and dashboards that may be published to the Power BI Service (which you may recall is the cloud-based service where you will typically upload your data, run cloud-based analytics tools, and share reports and dashboards).

Anyone can get started on Power BI. The interface is highly intuitive, and with practice, you will soon become efficient and effective. Power BI offers a ribbon with almost every function you need to transform a dataset and provides a list of APPLIED STEPS where you may track your work, as shown in the screenshot below.

As you become more experienced, you may be seduced into working with the Advanced Editor, which is backed by the Power Query M formula language, a case-sensitive, data mashup language used to transform your data. M is a step-by-step sequential language structure: usually, every line in M script is a data transformation step, and the step after that will use the result of the previous step, as illustrated in the screenshot below.

For most of us in accounting, this language is, at first, hard to understand. Fortunately, Power BI endeavours to make the Advanced Editor more user friendly, so you may see how the different parts of the query work, considering any hard-coded values, keywords, functions, and steps.

Furthermore, as Excel does with Visual Basic for Applications (VBA), Power BI makes M an easier language to learn with an AutoComplete interface. And now the Advanced Editor will prompt you with a list of objects and methods that may be used, as shown in the screenshot below.

This turns M and the Advanced Editor from being almost completely inaccessible to new users to something much easier for people to learn.

M is not the only language in Power BI, though. Data Analysis Expressions (DAX) allows for further data analysis, once transformations have been completed. This language is much preferred by analysts, as many of its functions and syntax are remarkably similar to Excel’s.

Where M is used in the Power Query Editor part of Power BI, DAX is used for many calculations to create reportable measures and key performance indicators (KPIs) in Power BI Desktop.

Not only do Excel Power Pivot and Power BI Desktop use the same DAX modelling language, but you can embed your Power Pivot reports simply into Power BI after their creation.

Insights, analytics, and machine learning

Power BI’s ability to streamline business processes alone can make it a valuable investment for many businesses, but it can go further. Rather than just replicating existing Excel workflows within Power BI, Microsoft’s business intelligence tool can study large volumes of business data to recommend new ways to analyse the data, extract more granular insights, and visualise it. Even in Power BI’s free version, users have access to 10GB of data storage, with a 1GB limit per dataset directly imported into Power BI or 250MB if analysed via Excel.

When you import your data into Power BI, it can run a series of analytical tests to search for things like outliers, trends, and other insights. While some companies do not and will not use Power BI’s insight tools to produce reports, these tools can still help them refine the questions that they’re asking of their dataset(s).

For example, Get Quick Insights is a powerful, one-click tool. Once you upload your data to the Power BI Service, you can let Microsoft servers run their processing power over your dataset, searching for outliers, correlations, trends, and so forth. This will result in a series of up to 32 insight charts that can help explain something interesting about your dataset (see the example below).

The descriptions may not always be the best, but you can change those easily enough. This has been so popular, that a similar offering is now available in Excel, called Ideas.

Power BI also includes support for natural language queries in its Q&A feature. The power of Q&A is to be able to ask questions in plain English, with no programming knowledge required whatsoever. For example:

Or, if your dataset permits:

These charts appeared automatically. Traditionally, these above questions might be asked by finance managers at a meeting and you would need to add that request to the reporting package for the next meeting, but with Power BI you can answer that kind of question on the spot within seconds.

This is an incredibly powerful tool that lets people ask their own questions and seek answers, without needing to wait for report owners and management accountants to set up reports first.

To use Q&A, click on Q&A in the AI visuals group on the Insert tab (see screenshot below).

Another feature to know is called Analyze. If you have a single metric over time, you can right-click on one of the time periods, click on Analyze, and ask it to explain the increase (or decrease in the values). Power BI Desktop then analyses the contributions to the differences and offers you a series of waterfall charts (with the option to use other types of charts) to help explain why values have changed.

How long would that take you to do!?

Word to the wise

Power BI continues to evolve. The Power BI team is seeking to continue to improve and provide innovative instant data visualisations and identify trends, outliers, and forecasts in real time. In just the past month, smart narratives have been announced in Preview form where Power BI will even generate customisable text and useful automatic insights in narrative form:

The future is coming, and it’s coming now.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.