Turn your Excel Power-Users into Power BI Superheroes

Damiem Chamness - 5 minute(s) read.

Your Excel users do important work. Your Excel “Power-Users” do inspiring work. They may not know it yet, but they both are primed to elevate your business insight capabilities using what they already know. This is because Excel’s most powerful features have been re-imagined with scale in mind in Microsoft’s Power BI Service, and your Excel Power Users are surrounded with use cases ready to make the jump. You just have to ask them.

In this article we will first draw a direct line between Power BI’s feature set, and their Excel origins, as a way of explaining where your Excel-to-Power BI use cases begin. We will then share an iOLAP client story where interviewing one Excel Power-User led to an opportunity to eliminate hundreds of scheduled and ad-hoc reports with one Power BI Solution. We’ll close by examining what are the factors behind “Power BI Project Readiness” and why your Excel Power-Users are uniquely equipped to contribute.

If your Excel Power-Users are at all familiar with the Excel features known as Power Pivot and Get & Transform (also known as Power Query), then they already have experience with Power BI’s core feature set and the languages (DAX and M) that power them! Power BI, released to the public in 2015, was essentially spun off from these “Power” features in Excel 2013*, which added significant database and graphical capabilities to Excel’s ever-expanding footprint. Despite this humble beginning, Power BI has gone from being a catch-up software in the race for augmented analytics technology, to becoming the Microsoft’s keystone to realizing a full-fledged platform for Analytics and BI within the Office ecosystem. In fact, Microsoft has taken a commanding lead both in terms of vision and ability-to-execute in this space, as awarded by Gartner’s Magic Quadrant[1].

So where do Power BI’s strengths begin, and Excel’s end? It’s first important to recognize what role Excel plays to a Power User. A keen comparison could be drawn, of all places, from the professional kitchen; namely, the chef’s knife. Most cooks wouldn’t trade their chef’s knife for anything, because it’s an extension of themselves that has rewarded their mastery of technique. Try to sell them on a slice-and-dice mandolin, a complicated food processor, or even a handy garlic press, and they may respond with surprising methods to do the same thing with no more than a cutting board and a knife in hand. So too is the relationship between software and Power-User. For daily driving, rapid prototyping and small-to-medium scale data volumes, Excel can become great when applied with skill. Where Excel falls short is when those same features become fickle and easily broken, thus disrupting flow. A tool that dared to enter their daily routine must broaden their capabilities without snuffing out what makes them great in their original skillset. For our cook it might be a stand mixer that can knead bread and churn ice cream unattended, thus giving back their time and saving their shoulders from injury. For the Excel user, how about a beautiful mobile dashboard without gimmicks, that replaces the daily tedium of fixing a PivotChart on SharePoint Excel Services, thus saving them grief from their boss? What if they could leverage their core competencies into big accomplishments?

Where the real strength of Power BI comes from, is in those features that may not even occur to an Excel Power-User, who may be used to employing their ingenuity with VBA to solve complex problems. An iOLAP client story tells us about the value of consulting directly with Excel Power-Users and helping them to interpret use cases that exceed their original articulation of the root problem. A fast-casual restaurant chain contracted iOLAP to develop a Power BI solution to replace their previous system in a one-for-one feature parity in order to enjoy the benefits of improved visualizations and ease of use. The universe of users was small: VPs, District Managers (DMs), and one lone financial analyst. Upon iOLAP’s unveiling of the first iteration of the solution, the analyst spoke up with a previously undisclosed need: they needed the ability to export large amounts of data in a format identical to their prior system, in order to use a VBA script to generate individualized store-level reports catered to each General Manager (GM). That same VBA script would then email the reports to the GMs, one by one.

On further examination, the root cause emerged: GMs were expected to see only their own store’s data, not others. Similarly, the DMs were allowed only to see information for their own district; they frequently made ad-hoc requests as well, which had to be carried out manually by the analyst. Because Excel lacks any meaningful security (especially at the row level), no one report could be exposed to all of the Managers, even in a web context. Stated plainly, there’s no good way in Excel to filter data down to what one user is supposed to see, while preventing them from also seeing others’ data.

iOLAP would soon propose and develop a solution using Power BI’s RLS (Row-Level Security) feature. An administrator (the analyst) would be able to assign users and groups to roles in the Power BI service; they can then write DAX expressions (similar to Excel functions) that dynamically expose data based upon which user is active. Now the GMs could be brought on to Power BI services and presented with up-to-date dashboards with exactly the data they’re meant to see. Furthermore, the analyst could interpret ad-hoc requests from DMs into design-once/use-forever dashboards. And yes: Excel extracts would still be available to every user, only this time as a self-service feature in Power BI. All this meant that the client’s KPI distribution strategy was no longer tied to VBA scripts; instead a robust business intelligence framework would take its place, with an Excel Power-User-turned-Power BI Superhero analyst at the helm.

It’s no mistake that consulting with an Excel Power-User was so fruitful, and their transition to Power BI so promising. Because they forge their skills on the frontiers of their organization, where business logic is being codified and data wrangling is an artform, their work makes possible the determination of BI Project Readiness[2]. Their best workbooks are living case studies, complete with feasibility assessment with respect to data availability and quality. Most importantly, they are vital partners for championing progress because they can speak to what’s to be gained – time saved, risk reduced, and much more.

Power BI presents a clear on-ramp for your Excel Power-Users to level up their capabilities, not only in a hands-on sense but also as a stakeholder in broader analytics projects. Because so many of Excel’s features map directly forward to Power BI, they’ll often find their existing workbooks are prototypes for Power BI’s dashboards. And because Power BI provides a best-in-class analytics platform, the lessons learned from using it will make them better Excel users in turn.

* This is an abridged telling. Those “Power” features trace back to SSAS (SQL Server Analysis Services). In fact, Power Pivot is literally an embedded version of SSAS within Excel.