Reefs of use of Excel Power Query and MySQL for automation of the reporting2 years, 7 months ago
There came new 2016, so it is time to update tools for simplification of boring mechanical work. Departments of analytics, marketing, sales often face the following difficulties when updating the reporting:
1. Data should be aggregated from several sources.
2. Reports are formed in Excel that imposes considerable restrictions for volume of the processed data.
3. Modification of the unloadings which are in advance configured by developers business as a rule not the faster.
If reports need to be updated weekly or even daily, then this procedure becomes very napryazhny even for the most patient. By means of a superstructure of Excel Power Query and data record in MySQL it is possible to reduce updating of the majority of reports before simple clicking of the Update button:
1. Data from any quantity of sources are imported through SQL queries to the normal tables Excel.
2. Even from big base it is possible to write in Excel only small part of data (for example, total sums for the necessary range of dates with grouping only on the necessary columns).
3. Changes can be made to the report just having changed the SQL query. Further we create the necessary report standard means of Excel.
I will show how to configure and autocomplete the simple MySQL databases (on the example of unloading of statistics of all key word from Metrics Yandex) in this article, and then one button to update reports in Excel, using Power Query superstructure. Power Query has very strange features of work by drawing up SQL queries (especially dynamic) which we will sort in the second part of article.