Skip to content
Home » Excel Power Query Training in Surrey | Onsite Training in Surrey

Excel Power Query Training in Surrey | Onsite Training in Surrey

    Apologies - this course is not currently available.

    Established in 2005, Blue Pecan delivers Excel Power Query training at customers' business premises or online. Our one day Power Query training course gives delegates a good introduction to Power Query as a data connection technology. Delegates will learn how to discover, connect, combine, and refine data sources and so meet their data analysis requirements.

    We provide Excel Power Query training in Surrey including Woking, Guildford, Walton-on-Thames, Esher, Camberley, Redhill, Leatherhead, Epsom, Weybridge, Ashford, Egham, Staines, Farnham & Horley.

    Introduction to Power Query - 1 Day Course

    Power Query allows you to automate data tasks in three stages: extract, transform and load. This course gives a comprehensive introduction to these tools.  By the end of the course, delegates will be able to effectively extract data from Excel workbooks, CSV and text files, Access databases and folders.  Delegates will also be able to effectively clean, integrate and enrich data.  Finally, delegates will be able to load data to a connection, an Excel table or a Pivot report. Download the course outline in pdf format.

    Navigate the Power Query Editor (PQE)

    • Query Settings task pane
    • Formula bar
    • Query dependencies

    Select the Source Data for Your Query

    • Table/Range in current workbook
    • Data in another workbook
    • Text/CSV files
    • Files in folder
    • Access database
    • Blank query
    • Existing connections
    • Referencing existing queries

    Close and Load Queries

    • Load to a table
    • Load to PivotTable or Pivot Chart
    • Only create connection
    • Add to data model

    Edit a Query

    • Edit a query – launch the PQE
    • Understand steps in the Query Settings task pane
    • Delete steps
    • Modify steps
    • Name/rename a query
    • Delete a query
    • Refresh a query

    Transformations

    • Change a field’s data type
    • Remove rows/columms
    • Move to the top or bottom of a worksheet
    • Change a field’s data type
    • Remove rows/columns
    • Split columns
    • Merge columns
    • Sort columns
    • Filter columns
    • Perform calculations on columns
    • Create conditional column
    • Create new columns by example
    • Group columns
    • Fill data
    • Use first row as headers
    • Extract text
    • Trim/clean
    • Change case
    • Create index

    Append Data

    • Append all tables in the current workbook
    • Append specific worksheets within the current workbook
    • Append all worksheets in an external workbook
    • Append specific worksheets from an external workbook

    Combine Files in a Folder

    • Combine files in a folder where the sheet or table name are the same
    • Combine files in a folder where neither the sheet or table name are the same
    • Import Text/CSV files within a folder

    Other Transformations

    • Merge data across tables
      • Match columns
      • Select the correct join kind
    • Pivot data
    • Unpivot data

    Share Queries

    • Export a query as a connection file
    • Import a query as a connection file

    M Functions and M Language

    • View the M code for your query
    • Write M formula
    • Use M Text functions
    • Use M Date functions
    • Use more M functions
      • CurrentWorkbook
      • Workbook
      • Combine 

  •