Home » Excel Power Query Training in Dorset | Onsite Training in Dorset

Excel Power Query Training in Dorset | Onsite Training in Dorset

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 Dorset including Bournemouth, Poole, Weymouth, Christchurch, Ferndown, Dorchester, Wimborne Minster, Bridport, Verwood, Blandford Forum, Gillingham, Swanage, Sherborne, Shaftesbury, Wareham & Lyme Regis.

Course Feedback

[testimonials_cycle theme=”clean_style” category=”excel-training-in-dorset-testimonial” count=”-1″ order_by=”date” order=”ASC” show_date=”1″ hide_view_more=”0″ testimonials_per_slide=”1″ transition=”fade” timer=”5000″ auto_height=”calc”]

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 

  •