What is the most efficient way to clean a large volume of data?
Efficiency question :
Before deciding how to clean a large volume of data, first define what constitutes a large volume in the organization’s context.
Microsoft Excel can work with more than one million rows per worksheet. In practice, when the volume approaches or exceeds about 500,000 rows, some operations and calculations can become heavier and create processing delays.
The nature of the data must also be considered. A transaction file may continue growing every day, while a customer list may grow much more slowly. The current volume matters, but so does how it will evolve over time.
Key considerations:
Before choosing a cleaning method, determine how many rows must be processed and consider how that volume will evolve.
If new transactions are continually added, a solution that works well today may become less efficient as the volume grows.
Conversely, some datasets remain relatively stable. Row count is therefore not the only criterion: the nature of the data and its expected growth should also be considered.
Challenges:
Operations performed directly in Excel worksheets can become heavier as data volume increases.
Calculations, transformations and other operations may then require more time and resources.
The challenge is to avoid burdening worksheets with large processing tasks that could be handled more efficiently before the data is returned to Excel.
Technology:
Power Query is particularly well suited to cleaning and transforming large volumes of data.
Unlike an Excel worksheet, Power Query’s processing environment is not limited to about one million rows per worksheet. Data can be retrieved and transformed there before only the required results are returned to Excel.
This approach moves a significant portion of the cleaning and transformation work outside the worksheets.
Developer's advice:
Cleaning a large volume of data in Power Query relies on a sequence of transformations. Not all transformations, however, have the same impact on execution time.
Some transformations can be performed more efficiently at the source, particularly when they can be passed to a compatible data source such as a SQL database. Other transformations must be performed directly by Power Query and may require more processing time.
The order in which transformations are executed therefore matters. If cleaning a large volume of data takes too long, review the sequence of transformation steps.
By reorganizing certain steps and performing transformations that reduce data volume as early as possible, query execution time can often be improved significantly.