Boost business with Big Data? Excel can now do this!
Where former records were kept tidy and we were limited by the physical storage capacity of the “archive”; there is no barrier to retaining data today. Result: many terabytes of data waiting for an analysis.
Since the advent of the term Big Data, we are happy to share this with you. Extensive analyzes of data sets that were thought to have nothing to do with each other now result in new insights and predictions.
Provided that only the right software is used. MS Excel Power Query.
But for the data in smaller proportions in an average company (which is then Small Data), you must already come from good homes to make meaningful information of this information. Of course Excel can support us, with some vertical search, a turntable, cut and paste, and maybe some VBA code, clear data can be extracted from a foggy data set to boost your business processes. Unfortunately, the data provided is not always ready for use, ranking, removal of corrupt or unnecessary data and linking to other resources requires a lot of time and effort.
This has happened to Microsoft and, for example, since Excel 2010, the fantastic Power Query.
“Where were you all that time?”, Is the first reaction to the introduction. In versions 2010 and 2013, they can be downloaded as a free plug-in from the Microsoft website (please note the 32- or 64-bit version). In Excel 2016, downloading is no longer required, by default, the tool is already on the toolbar under the Data tab. Here is the name “Retrieve and Transform” or “Get and Transform”.
Sounds that new tool on our toolbar, what can we do with it? Generate information from a collection of data. It almost does not matter where and how the data is stored. First, make sure that the Power View tab is visible in the main menu.
Broadly, there are three steps to get information from a set of data:
- Indicate the source data. Many options, including: Excel files, CSV, SQL / Access databases, JSON, XML, text files, etc. Websites may also be used as sources for analysis.
2. Clean up and sort the data. Delete or add columns, make columns. Empty fields fill. Merge / split columns and more. But also link the sources selected in step 1.
3. Reload the created information to Excel, as a table or in the Excel Data Model for further presentation capabilities in, for example, a dashboard.
The nice thing is that these steps are saved as “query”. When data of the new month is available, the same steps will be taken automatically, which will save a lot of time and errors. See this as recording a macro, but then with steps in editing the data.
This tool greatly simplifies the processing of (large) data sets.
In summary, a very powerful but possibly unknown BI tool available in Excel. Can anyone just go with this tool? That’s not, but it takes some practice. Or let it be done by one of our specialists, you, of course, know how to find us. Click here for our contact details.