How do you consolidate data from multiple files?
Efficiency question :
When consolidating data from multiple files, the first step is to determine whether the data has the same structure or whether its format differs from one source to another.
When the data has an identical structure, several methods can be used to combine it efficiently. The choice depends mainly on where the data is stored and how it will be used after consolidation.
Once consolidated into a database format, the information becomes much easier to manipulate, analyze and use in different reports.
Key considerations:
When the data has the same format, three consolidation methods can be used.
The first, and probably the simplest, is to use multi-file formulas in Excel. These formulas can retrieve data from several files and be combined with functions such as SUM or AVERAGE to consolidate the results.
The second method applies when several files with the same structure are stored in one folder. Power Query can use the folder as the source and automatically consolidate the data contained in the files.
The third method applies to data distributed across several worksheets in the same Excel file. Power Query can retrieve the data from each worksheet and consolidate it into a single dataset.
Challenges:
The main challenge arises when the data to be consolidated does not have the same format.
Power Query transformation steps and the M language can then be used to bring the data into a common structure before consolidation.
Particular attention should be paid to data formats. Dates, for example, may be stored differently across sources and may require transformation before they can be compared or combined correctly.
When structures differ, a mapping table may also be needed to establish relationships between the different data and make them comparable.
Technology:
Power Query is particularly well suited to consolidating data from multiple sources. It can be launched directly from Microsoft Excel and can retrieve, transform and consolidate data.
When Power Query is used for consolidation, the data can be returned to Excel as a structured table that is ready to be manipulated and analyzed.
The M language provides additional flexibility when the data requires specific transformations before consolidation.
Power Query is integrated into Microsoft Excel and, at the time this sheet was written, using it in Excel does not require additional licensing fees.
Developer's advice:
Do not assume that all data to be consolidated has exactly the same structure. Check the different formats before choosing your consolidation method.
When structures differ, create a mapping table if necessary and use M-language transformation steps to standardize the data before combining it.
For practical consolidation examples, SLV Expert’s Power Query and Power Pivot for Controllers and Analysts course is a good introduction.
To go further with Power Query and the M language, the Power Query Level 2 course is also available through SLV Expert’s on-demand training portal.