Pobierz kartę szkolenia
MS Excel - Power Query –advanced data retrieval and transformation
kod szkolenia: EX PQ / ENG DL 2dThe training is intended for the people who would like to know additional possibilities of downloading data to Excel (for example from web sources), automation and transformation using M language.
- You will know what Power Query is used for.
- You will learn how to download data from different local and web sources.
- You will be able to consolidate data from several files (for example Excel, Csv, Access etc.).
- You will get to know the possibilities of working with data consisting of even 50 000 000 rows.
- You will learn how to perform operations on data with tools which so far have been unavailable in MS Excel.
- You will be able to use the query list in Power Query.
- You will see how to create query steps in Power Query.
- You will find out what is M language and why it is worth using it in Power Query.
- You will learn how to create your own functions using M language.
Successful navigation through MS Excel interface. The knowledge of basic tools and formulas, as well as basic functions.
English
- Introduction
- What is Power Query and its role in preparing data for Power Pivot model
- Discussing available sources
- Sourcing data – importing external sources
- From files (among others Excel, CSV, TXT)
- Relational databases (among others MS SQL Server, MS Access)
- Online services (among others SharePoint, WWW websites)
- Folders
- API keys downloaded from different web services
- PDF – only Office 365 version
- In-built tools for data transformation
- Dividing columns into different arguments
- Deleting/keeping verses
- Converting data to a heading
- Changing value/errors
- Transposition
- Grouping
- Merging columns
- Filling in empty cells
- Unpivot / Pivot of columns
- Data tranformation (among others the last day of the month, of the week, quarter, name of the month, names of days, year)
- Number transformation (among others, count unique values, divide completely, the absolute value, rounding)
- Text transformation (among others trim, clear, letter size, intelligent extraction)
- Operations on queries
- Refreshing
- Grouping
- Downloading options
- Editing
- Dependencies between queries
- Combining several data sources
- From several locations, files and sheets
- Merging queries
- Fuzzy merging – only Office 365 version
- Adding queries
- Adding columns
- Conditional
- From examples
- Non-standard
- From index
- Query parametrisation
- M language
- What is a language in Power Query editor used for
- M language syntax
- Ways of using the most popular functions
- Defining your own variables
- Converting queries to functions
- Creating new queries using advanced editor
- Non-standard solutions
- Consolidating structurally complex files
- Creating a calendar of dates with a possibility to use a displayed language of the names of the months and days
- Multi-parametric queries
- Using API keys which allow for example to download current data from Internet services (among others, banking services, weather forecast services, social services)