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.
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).
- Introduction to Power Query, its scope. The concept of a query, working in the query editor, the concept of the M language
- Using the result of the query. Update requests.
- Getting data from various data sources (text files, xml, json, Excel spreadsheets, Access databases, data from the Internet, odata, etc.)
- The principle of step-by-step data transformation in the query editor. Actions with steps (change, delete, add a new step).
- Table transformation (filtering, sorting, grouping, splitting columns, transposing, creating calculated columns, etc.)
- Working with text and numeric data. Working with dates and times. Duration data type and its usage.
- Working together multiple queries:
Bulk data loading:
- Adding data from multiple queries
- Merging (combining, linking) queries. Join types
Request management. Grouping, duplication, link to request. View query dependencies. Request protection.
M language. It's use to improve queries.
- Getting data from all (or many) files in a folder and its subfolders.
- Getting data from all (or many) sheets of a workbook.
- Getting data from all (or many) tables and named ranges of the same workbook.
Requests with parameters.
- The concept of the language "M", the scope of its application.
- Working in the advanced editor.
- Fundamentals of the syntax of the language M.
- Simple and structured data types.
- If branch statement.
- Each keyword.
- Built-in language features. Using help on built-in functions.
- User functions.
- Absolute and relative references in queries.
- Error processing.