Regardless of the people job title, preparing and analyzing data becomes an integral part of their work. Most people for this purpose use Excel, because it is the fastest and most flexible tool for processing and analyzing data. But as the data sources and analysis needs increase with each day, traditional methods of processing and analyzing data in Excel become slow and limited.
Of course, there are PowerQuery (for data processing automation) and PowerPivot (for analyzing large amounts of data) tools in Excel version 2010 and later, but how many people use them, or are aware of its existence?
People do not use these tools because:
- They learned to work manually in Excel and consider it fast enough,
- As soon as they hear PowerQuery or PowerPivot they think that these are new technologies that are difficult to learn but do not provide enough benefits to start the learning process.
The thing is, in fact, that both tools are extremely easy to learn and they increase the quality and speed of analysis/reporting to a brand new, modern BI level.
Why did I start with Excel?
Because PowerBI is a natural upgrade to Excel. It is designed to help Excel experts get into the modern era of the large data analysis. The PowerBI tool is based on technologies developed for Excel (PowerPivot and PowerQuery). Microsoft later added beautiful visualizations and Cloud Reporting Platform to these technologies to create a comprehensive data analysis tool.
PowerBI is the cherry on top of the technology developed over the last 20 years and tested in Excel before it came fully to life in PowerBI environment.
Why PowerBI?
There are many BI tools currently available on the market, each with its benefits and flaws. The fact is that sooner or later you will need to start using one of them! You would want to consider PowerBI because:
- You need simple but at the same time powerful tool that can be implemented and used by your employees, without the need of hiring external IT companies.
- You need a tool that can easily fit into your existing business analytic solutions.
- You need a tool that is perfectly complemented with Excel. Excel is and will be the main tool for ad-hoc analysis and quick/flexible reporting. Other BI tools are trying to minimize or eliminate the need for Excel (and all fail in an attempt) while PowerBI works in perfect synergy with it.
- The key components of PowerBI (PowerQuery for data processing and PowerPivot for data modeling) can also be used within the Excel interface, which greatly increases the productivity of regular Excel users!
PowerBI can help you to:
The main PowerBI tools are Power Query and PowerPivot
PowerQuery
PowerQuery, also known as Excel’s most powerful data tool, is used for extracting, transforming and loading of raw data.
It Replaces all Excel data-cleaning related formulas (such as TRIM, CLEAN, SUBSTITUTE, LEFT, MID, CONCATENATE, PROPER) and all Excel functions related to the data structure (merge, fill, filter, format…). And best of all, PowerQuery is able to record all the steps of transformation. When new data arrives, all you need to do is to click a refresh button! PowerQuery will apply all the recorded data transformation steps to newly added data, eliminating the repetitive work previously done in Excel. In this way, you can shorten the time you spend on processing and structuring data by more than 90%.
The tool is very easy to learn because a large part of the use of the tool is in principle what you see is what you get. Very quickly (after a few hours) you can create your own data processing automation scripts.
PowerPivot
Are you familiar with VLOOKUP? Then you know that the main function of this formula is to merge data from two different tables. Most of the time you would bring data from separate tables to your main table, on which you would create a PivotTable, then do some data analysis. Did you know that by using VLOOKUP and PivotTable you actually made a small data model?
But the lack of a VLOOKUP/PivotTable approach is that (apart from VLOOKUP being extra slow when used on more than 100 thousand rows of data), all data should be in the single table within Excel. That’s ok if you have a couple of thousand of rows of data and 3 different sources, but what if you have a couple of tens of millions of rows of data and 10 sources that you would like to correlate to be able to do analytics? In that case, VLOOKUP/PivotTable does not make too much sense.
In that case, you need a tool that can import over 100 millions of rows of data, link 100 different sources, and then make an analysis on top of it, using just a regular laptop. A tool that can do that, and much more, is called PowerPivot.
It is available in Excel versions 2010 and above and is the main PowerBI tool. I’m surprised that most people do not know about it even though they use Excel in their daily data analysis.
Using PowerPivot, you will never need a VLOOKUP again. You will merge all the data (previously imported and cleaned through PowerQuery) into a single model that will give you quick and easy answers to all of your questions!
When to use PowerBI and not the PowerQuery/PowerPivot combination in Excel?
Excel is a multifunctional tool that allows easy data entry and editing of data structures within the Excel interface. If we add programming with VBA, then Excel capabilities are very diverse. In that case, you will use PowerQuery/PowerPivot as auxiliary tools in shaping your Excel solution (creating Excel applications or add-ons, custom CRM solutions, smaller automated systems …), which is actually a common practice.
If you want to use PowerQuery and PowerPivot with focus on the data analysis, then it’s best to recommend using the PowerBI tool.
Why?
- Easier navigation inside the app (no cells, no 10 different ribbons …). All you see is related to the analysis of your data. No confounding factors in the analysis.
- Simple and secure sharing options via a web platform (online access or through the Android/iOS app). No more sharing of Excel files via e-mail.
- Visualization seems incomparably better.
- Constant Microsoft investments in the further development of the tool.
Conclusion
If you use Excel in your daily work, you should start using PowerPivot and PowerQuery because it will greatly speed up, facilitate and improve your work within the Excel. If you are primarily oriented to data analysis, then you can greatly benefit from PowerBI which is the future of Microsoft self-service/cloud business intelligence!
If you have any questions, feel free to comment below!
If you liked the article, do not forget to like/share!
