Course NT-PowerQ Extracting and transforming of data with Power Query
Power Query is a powerful data tool that lets you easily extract, clean, and transform information from a variety of sources. Previously Power Query was add-in for Excel, but since 2016 it has become part of the standard functionality of Excel, receiving the name «Get and Transform».
This tool allows you to create queries that execute two main functions:
- Extract data from various sources - from simple files (CSV, Excel) to cloud storage, databases, web pages, social networks networks (for example, Facebook, Google Analytics) and many others.
- Transforming data into a form that is convenient for you so that it can be used for further analysis.
Power Query allows automate work with data. Instead of manually repeating actions, it is enough to set up a query once and update it in a few clicks. This significantly saves time and simplifies the process of working with large volumes of information.
This tool allows you to process data much faster than traditional methods in Excel, where you had to write complex formulas or create macros in VBA. With the help of Power Query all these tasks are performed in several clicks.
Note that Power Query is basic part Power BI Desktop and if you plan to work with this data analysis tool in the future, this course will be a great preparation.
Our Training Center has several programs for study. Power Query – from basic to advanced, allowing you to master both the basics and more complex techniques for working with data. You can choose the course that best suits your needs and level of training.
This course will not only help you learn how to work with Power Query, but also to understand how to make data processing faster, easier and more efficient.
Also study basics Power Query you can during our course on using Power BI Desktop, service Power BI, DAX and Power Query How tools Business intelligence.
If you need more, we have advanced course, which will allow more deeper master the possibilities Power Query and M! language
After completing this course, students will be able to:
- use data from many external sources in Excel: text files, Excel workbooks, databases, web pages, etc.;
- use powerful and convenient sequential data transformation in the query editor;
- prepare data for convenient use in the future;
- collect data from many objects at once, such as from many(or all) files in a folder, or from many(or all) sheets or tables in a workbook;
- use the language M, which will allow you to complicate queries created using editor commands.
Audience Profile
The course on the basics of Power Query will be of interesting for:
- Data analysts and financial professionals who want to automate their work with large volumes of information and speed up the processing of it.
- Economists, logisticians and other specialists who analyze large volumes of data obtained from various sources.
- Business users who work with Excel and want to optimize routine data processing operations.
- IT specialists who integrate data and seek effective solutions for information transformation.
- Professionals who plan to learn Power BI because Power Query is a key data preparation tool in Power BI Desktop.
- Managers and executives who want to better understand the capabilities of Power Query for business process optimization.
The course is suitable for those who are just starting to get acquainted with Power Query, as well as for anyone looking to prepare for a deeper dive into data analysis and business task automation.
Before attending this course, students must have:
Basic skills in Windows and Microsoft Office.
Also important have confident level possessions Excel (summary tables, VLOOKUP, sorting, filtering, intermediate results, «smart tables»).
These skills will help you master the course material more quickly and effectively apply the acquired knowledge in practice.
1. Introduction: What is Power Query, its scope of application. The concept of a query, working in the query editor, the concept of the M language.
2. Using the query result. Updating queries.
3. Obtaining data from different data sources(text files, Excel tables, data on the Internet, etc.).
4. The principle of sequential data transformation in the query editor. Actions with steps(change, delete, add a new step).
5. Transform tables(filtering, sorting, grouping, splitting columns, transposing, creating calculated columns, etc.).
6. Working with text and numeric data. Working with dates and time. The duration data type and its use.
7. Multiple queries working together:
- Adding data(append) from multiple queries.
- Merge(unification, connection, merge) of queries. Types of unions.
8. Bulk data upload:
- Getting data from all(or many) files in a folder and its subfolders;
- Getting data from all(or many) sheets of a workbook;
- Retrieve data from all(or many) smart tables in a single workbook.
9. Language M. Its use for query refinement.
- The concept of the language M, the scope of its application.
- Working in the advanced editor.
- Basics of the syntax of the M
- Simple and structured data types.
- If branching operator.
- The each keyword.
- Built-in language functions. Using built-in function help.
- User defined functions.
10. Requests with parameters.
Sign up for the closest date
Copyright © 2024 Networking Technologies