Course NT-PowerQ Extracting and transforming of data with Power Query

Power Query is one of the Excel add-ins. Starting with Excel 2016, Power Query becomes part of the base functionality and changes its name - now it's called Get and Transform, "Download and Transform".

As a rule, working in Power Query, you create what is called a query (or multiple queries) that does two things.

The first is the extraction of data from numerous sources: files of various formats stored locally or in cloud storage, databases, web pages, web services, Facebook, Google Analytics and so on - the list of these sources is very large and constantly updated.

The second task is to transform this data into a certain form that you need, making it possible for their further convenient use.

The actions that make up the request can be performed once, but if they need to be repeated in the future, it will be enough to update the request with one click.

Power Query greatly extends the functionality of "traditional" Excel. In many situations where preparing data for use (for example, to build charts or pivot tables) would require laborious manual work, or writing large formulas of increased complexity, or even creating macros in VBA - Power Query handles the problem with a few clicks of the mouse or a few touches of the keyboard.

To sum up: this training is about extracting and transforming data through Power queries Query.

Note that such a popular analysis tool as Power BI Desktop, it uses Power to obtain and transform data. Query, and if you plan to master Power BI Desktop in the future, then this training is for you.

It should also be noted that the fundamentals of Power Query are covered in the NT-Excel BI course. Power BI "Business Intelligence with Microsoft Excel and Power BI", but on a much smaller scale.

After completing this course, students will be able to:

• use data in Excel from a large number of external sources: text files, Excel workbooks, databases, web pages, Facebook, Google Analytics, and so on;
• master the strongest and most convenient step-by-step transformation of this data in the query editor;
• bring "bad" data to the form that will allow you to use this data in the future (by sorting, filtering, splitting columns, replacing values, etc. in the query editor);
• collect together data from many objects at once, for example, from many (or all) files in a folder, from many (or all) sheets or tables of a workbook;
• apply the basics of the M language, which will allow you to complicate queries made in the "standard way" using editor commands.

Audience Profile

The course will be useful for analysts, economists, logisticians, financiers and other professionals whose tasks include the preparation and analysis of large amounts of data obtained from various sources.

Before attending this course, students must have:

• experience in Windows and MS Office
• good skills in Excel (pivot tables, VLOOKUP, sorting, filtering, subtotals, smart tables).