Pobierz kartę szkolenia

MS Excel - Power Query –advanced data retrieval and transformation

kod szkolenia: EX PQ / ENG DL 2d

poziom Średnio zaawansowany

W celu uzyskania informacji skontaktuj się z działem handlowym. W celu uzyskania informacji skontaktuj się z działem handlowym.
1 700,00 PLN 2 091,00 PLN brutto

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)