Microsoft Excel. Level 7. Creating Power Query queries: data transformation and consolidation - course RUB 24,990. from Specialist, training, Date: December 2, 2023.

Special purpose teacher, holder of prestigious international status Microsoft Certified Master. Graduate of Moscow State Technical University named after N.E. Bauman.

Experienced and demanding teacher, focused on high results. Author of the only authorized Microsoft Excel training courses in Russia. The tutorials compiled by Olga Vladimirovna have been established by Microsoft as the Excel training standard.
He sees his task as a teacher in so that you can say: “I can do it!” I know how to do it! Achieving this result requires extensive teaching experience. Add to this passion for your work, and you get the formula for an ideal teacher!
Graduate of Moscow State Technical University named after N.E. Bauman. Regularly attending conferences gives her a deep understanding of the products taught, revealing the most relevant areas of application of their capabilities. Work experience allows us to achieve the target result in mastering the material with students of various levels of preparedness, ages, professions and interests. Practical assignments allow you to make learning Microsoft Office programs as interesting and effective as possible for each student. The original examples used for training are focused on solving current problems of the modern office.
Olga Vladimirovna implements educational projects of any complexity for leading Russian and foreign companies. Among its more than 14,000 graduates are employees of such companies as Loreal, Tetra Pak, Schneider Electric, Volkswagen Group Rus, Gedeon Richter-Rus, Moscow Interbank Currency Exchange, Australian Embassy, ​​Walt Disney Company CIS, Samsung Electronics Rus Company, BANK URALSIB, MICHELIN Russian Company tire production, Nexia Pacioli Consulting, Air Rianta International Duty Free, Diva, Mobile TeleSystems, Housing Mortgage Lending Agency, Janssen Pharmaceuticals N.V., Robert Bosch, MegaFon, Johnson & Johnson, BSH Household Appliances, Colgate-Palmolive, Mosenergo, Tetra Pak CJSC, Home Credit and Finance Bank LLC, and a lot others.
Olga Vladimirovna is the author of publications:
Author of the Microsoft Excel 2019/2016 course. Level 6. Business analytics using Power Pivot, Power Query and 3D Map.
Author of teaching materials for Microsoft Excel courses:

A talented practical teacher, a certified Microsoft trainer, holder of several prestigious international statuses, including Office Excel® Expert and Office PowerPoint® Expert.
He has been teaching since 1999. During her career, Anna Evgenievna conducted training for employees of Raiffeisenbank, Rosgosstrakh, etc. Under the guidance of such a teacher, you can easily master even the most complex functions of Microsoft Excel and significantly improve your professional level.
Anna Evgenievna has an MBA degree in management. A specialist with more than 15 years of practical experience in strategic and operational planning, inventory management, marketing support for retail chains and small wholesale divisions. She worked in large companies producing and selling various product categories, where she was involved in promoting brands, procurement planning, optimization of stocks in the positions of product manager, leading analyst, production manager department, etc.
He has extensive experience in the field of analytics, planning, forecasting, and has the skills to both independently create a package of analytical reporting and lead a development team.
During her classes, Anna Evgenievna immediately creates a positive atmosphere of partnership, which is noted by students. She is happy to pass on her deep knowledge and experience to her students and shares with listeners really working and useful tools, personally tested by practice.

Module 1. Power Query Basics. Loading data (4 ac. h.)

-Assigning Power Query to work in Excel and Power add-ins
-Power Query interface
-Features of connections to various types of data sources: Text files MS Excel files WEB (Internet) databases XML files
-Text files
-MS Excel files
-Database
-WEB (Internet)
-XML files
-Request structure
-Simple transformations in the query
-Loading the result of a request: Selecting the download type when creating a request Changing the download type Setting the default download type
-Selecting the download type when creating a request
-Changing download type
-Set default boot type
-Query update: manual or automatic

Module 2. Language M (Power Query Formula Language) (3 ac. h.)

-Structure of the query algorithm and syntax of the M language
-Data types Simple data types Composite data types: tables, lists, records Conversions between different data types
-Simple data types
-Composite data types: tables, lists, records
-Conversions between different data types
-Conditional operator
-Error processing
-Operations on lists
-Operations on tables

Module 3. Query parameterization (1 ac. h.)

-Manage query parameter from Excel sheet
-Create and use a parameter table in an Excel sheet

Module 4. Solving problems of data structure transformation (4 ac. h.)

-Operations with data: Deleting, replacing and filtering Combining and splitting Transformations with text, numbers and dates Removing duplicates
-Removal, replacement and filtering
-Combination and division
-Convert with text, numbers and dates
-Removing duplicates
-Table transformations: Pivot (pivot by column) Unpivot (cancel column collapse) Transpose Row grouping Built-in and user-defined functions
-Pivot (pivot by column)
-Unpivot (cancel column collapse)
-Transposition
-Grouping rows
-Built-in and custom functions

Module 5. Working with multiple requests (1 ac. h.)

-Combining requests
-Adding requests
-Grouping requests

Module 6. Solving problems of consolidating data from different sources (3 ac. h.)

-Automatic consolidation of binary data: Text files Excel files
-Text files
-Excel files
-Consolidation of normalized data from different Excel workbooks: Developing a query based on data from one tables Converting a query into a function with variables Creating a query to obtain -consolidated data
-Development of a query based on data from one table
-Converting a query into a function with variables
-Create a query to obtain consolidated data
-Consolidation of data from different files/sheets with transformation of the data structure: Development of a normalization query data structures Converting a query into a function with variables Creating a query to obtain consolidated data
-Development of a request to normalize the data structure
-Converting a query into a function with variables
-Create a query to obtain consolidated data