virtual classroom
Contact
Contact
virtual classroom cart
Show training

MS Excel - Power Query –advanced data retrieval and transformation

training code: EX PQ / ENG DL 2d / EN
training assurance
For more information, please contact the sales department. For more information, please contact the sales department.
1,700.00 PLN 2,091.00 PLN with TAX

The 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

  1. Introduction
    • What is Power Query and its role in preparing data for Power Pivot model
    • Discussing available sources
  2. 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
  3. 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)
  4. Operations on queries
    • Refreshing
    • Grouping
    • Downloading options
    • Editing
    • Dependencies between queries
  5. Combining several data sources
    • From several locations, files and sheets
    • Merging queries
    • Fuzzy merging – only Office 365 version
    • Adding queries
  6. Adding columns
    • Conditional
    • From examples
    • Non-standard
    • From index
  7. Query parametrisation
  8. 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
  9. 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)