Training agenda
  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)