Many a times data downloaded from Applications/ERP’s are not in a filter/Pivot ready format. In such cases, a lot of time has to first be invested in getting that data in proper order before even beginning to analyse that data. What makes this situation worse is that data is downloaded every month in that unstructured manner and the effort which was spent on “cleaning up” the data in the previous month has to be replicated this month as well.
Here is one such dataset. Some easy to spot problems here are:
- All data is in a single column instead of being in multiple columns
- Data which should ideally appear in a single row is appearing in multiple rows. See data in rows 26 to 32. Information from rows 27 to 32 should appear in row 26
- Rows 1 to 6 are not required at all
Data should appear in the following format to make it “filter/Pivot ready”. Furthermore, when multiple such files (as the one shown above) are placed in a certain folder, one should simply be able to open the output file (shown below) and go to Data > Refresh All.
I have been able to solve this problem using Data > Get & Transform. Following these steps to get my solution up and working on your system:
- Download the input file from here
- Download the output file from here
- Create a folder on your desktop and place the input file in this folder. Store as many input files in this folder. Change data in those input files, if you so wish.
- Save the output file anywhere outside the folder that you created in step 3 above
- Ensure that all input workbooks are closed
- Open the output file and go to Data > Queries and Connections. Right click on the Result Query > Edit
- Double click on Source and select the folder that you created in 3 above
- Click on Close and Load
- Anytime there is any change in the source folder files, just go to the Output file and click on Data > Refresh All
Tabulating data from multiple unstructured Excel files
{ 0 Comments }