MS Excel – Analysing Data using Power Query

Content

Overview

• What is Power Query?
• Understanding data sources
• The queries and connections pane
• Loading V Connecting
• Accessing the query editor

Loading Data Sources into Excel

• Text/CSV
• Excel
• Web/PDF
• Folders

Transforming Columns and Rows

• Understanding row and column structure
• Adding removing and moving columns
• Merging and splitting columns
• Keeping and removing rows
• Sorting, filtering and grouping content

Transforming Content

• Understanding data types
• Text transformations
• Number transformations
• Data transformations

Transforming from Multiple Tables

• Working with multiple table data sources
• Appending tables
• Merging related tables

Managing Steps

• Understanding the query steps interface
• Modifying a step
• Adding and deleting steps
• Introduction to “M” language

Creating Dashboards

• Tables
• Pivot table and Pivot charts
• Slicers

Using the Data Model and Power Pivot

• Understanding Power Pivot
• Transforming source data to the data model
• Working with table relationships in the data model
• Using Power Pivot for analysis in Excel

Learning outcomes

• Understand what Power Query is, and how it can be used to transform data.
• Understanding the core functionality of Power Query.
• Understanding how Power Query can be used to help turn difficult data dumps into useful tables for further analysis.
• Examine some of the ways to do further analysis of this data using Pivot Tables and Power BI.
• To gain practical skills and practice with the above, using sample and specific data.

Further development

Microsoft Power BI Training – The Essentials | Discover how to import data from a wide variety of sources such as Excel, databases and web sites.  Perform powerful data analysis using the tools within Power BI. Visualise vast amounts of data and create stunning interactive data dashboards to empower the decision maker.