The tool that will kill Excel… and change your life!
In my analyst life, there was a before and an after I discover PowerBI
Before with Excel: My go to tool was generally Excel. It’s a very powerful tool for Reporting and Ad-Hoc analysis, however it has a:
- lack of connectivity: despite some basic connectors (Access…), data rarely comes to Excel by itself for analysis. You generally have to extract it manually from your systems (like SAP…), copy paste it manually, or VLOOKUP it which is a hassle
- lack of capacity: Although an Excel spreadsheet fits 1m rows which is enough for most users, this sounds prehistoric at the age of Big Data.
- lack of scalability: analysis and reports are rarely carved in stone, you constantly want to add on to it: new features, new drill downs, new information that you did not think you needed before… although this is not the weakest point of Excel, it can be quite a hassle to make a big Excel report evolve without having to destroy and rebuild big part of it
- lack of sharing options and governance: when an Excel analysis is done, how do you share it? Usually you will attach it and send it by email, or drop it on a shared drive for people to access. Again in 2017 that sounds a little outdated… but what if you would like to share only some part of the data to some users and some other part to other users? (for example you are have a global role and did a global analysis but you would like to share the US results with the US team only and the Asia ones with the Asia team only). Are you going to build 2 separate Excel files (hard coded I mean because they would easily access the global data if you just provide them with a Filter / Slicer)?
Don’t get me wrong, Excel is still an awesome tool and I still use it a great deal. The great news about PowerBI is that is that despite my provocative title it’s totally Excel compatible. As a matter of fact it is a Microsoft tool which started as an Excel add-in (for those who would remember PowerPivot) and evolved into a stand-alone BI tool
After with PowerBI: you will:
- connect to your data source seamlessly: there are plenty of connectors (Excel, Access, SAP, Hadoop…) available which enable you to be plugged directly on the data source without having to extract or copy paste it manually
- transform your data seamlessly: PowerBI comes with a built in ETL (Extract-Transform Load) tool which allows to transform and prepare your data to be used, but also to memorize each step of the transformation for next time
- manage lot of data: there is no capacity limit to the number of rows per table PowerBI can process, so it works well on very large databases
- scale easily: the data modeling tool in PowerBI will allow you to scale up easily if you need to add more information to your initial model. It’s very easy to add tables, connect them together, and then add a few more calculations
- build great visuals: charts and graphics are worth 1,000 words and great analysts know that it is important to have beautiful visual outputs to present your data. PowerBI has lots of it: line chart, bar charts, funnels, combo charts, pie / donut chart, maps with geographic location management… you can hardly do better. And when you think you’ve exhausted all options you can go online to the PowerBI visual gallery and download cool custom visuals developed by the PowerBI community
- explore with dynamic drill down: This feature exists in Excel with regular Pivot Tables, however you will find out that PowerBI takes it to a whole new level
- share and govern like a pro: with PowerBI you can share data online and users can access it online as well or via apps on their smartphone or tablet. when sharing you can define profiles and assign each user to a profile. Governance you said? Sorted!
- have fun! Whereas building an Excel report can be quite boring, you’re likely to discover that doing the same thing with PowerBI is fun, playful and exciting! No doubt you will wow your coworkers, boss and IT department when they see you displaying your slick reports on your smartphone!
Or because pictures speak 1,000 words that’s how my reports looked
Before with Excel
After with PowerBI
OK Excel is so 2000… but why not use a different BI tool?
If you have access to them (because they are not free…), there are a great deal of BI tools available today (Tableau, Qlik… to name a few), which if you master them will give you equivalent functionalities as PowerBI. You are more than welcome to use them especially if your company or organization provides them, however as we are supporting the masses of analysts out there who are left out with only Excel at their disposal, we will cover mostly PowerBI in this blog for the following reasons:
- It’s free! The Desktop version of PowerBI can be downloaded for free from the PowerBI Desktop download page. And if you want to use the cloud service to publish your reports or for other user to consume the content you will publish out there, you can get Free licenses.
- It’s the best! and we are not the only ones to think that way. PowerBI ranked #1 BI tool in the famous Gartner Magic Quadrant in 2016 and 2017
- It’s almost like Excel! Did I mention that PowerBI is the evolution of successive upgrades from ExcelExcel -> Excel + Pivot Tables -> Excel + PowerPivot -> PowerBITherefore Excel users will find it very natural and easy to transition from Excel to PowerBI. It will make it very easy to teach your coworkers about it.For example for 2 of the main functions you will be using:
Excel: VLOOKUP (Value, Table, ColumnNumber to look up)
PowerBI: LOOKUPVALUE (Column to look up, Table, Value)
Excel: SUMIF ( Column to sum, Filter)
PowerBI: CALCULATE ( SUM (Column to sum), Filter)
- It’s getting better every month: The PowerBI teams releases monthly upgrades to their online service and to PowerBI Desktop. The pace of innovation is quite crazy, think about it: it’s as if you were getting a new version of Excel every month! you can discover more about the different updated on the PowerBI blog
PowerBI is also a tool built based on the feedback of a community of thousands of users who are proposing ideas and voting for the best ones so that they can get picked by the PowerBI team to develop
So believe us, download PowerBI and start playing with it (we feel the word is not too strong). Surely you will soon be migrating all your Excel reports into it and next thing you know when asked for a quick ad-hoc analysis you will be using it as well and discover that Excel is really a tool from the past and that your life has changed…
We will cover more in detail PowerBI in next posts, stay tuned!