As analysts we can spend a lot of our time on repetitive tasks. Copying and pasting data from one file to another, cleaning and combining our data to produce regular reports. This is not only tedious and time consuming but it also can lead to errors.

Automating this work can free up our time to do more rewarding work and deliver extra value.

There are many systems out there that allow us to automate, but what if we don't have these. We can still do a great deal of automation using the power query functionality in excel.

In this box set we will cover    

1. Power Query Introduction

• This module introduces the power query box set and covers:
• An introduction to the concept of conditional probability and its role in interpreting and forecasting data.
• The historical background and theoretical foundation of conditional probability, as established by Thomas Bayes.
• The significance of considering underlying conditions in data analysis to avoid skewed conclusions across various practical scenarios.

2. Power Query - Data Structure
This module covers:

• Structuring data in Power Query for clear, detailed reporting and to identify improvement opportunities.
• Creating consolidated data sources for specific reporting needs, like agent dashboards, whilst considering data granularity and aggregation.
• Utilising data management techniques, such as table formatting in Excel, to simplify report building and ensure accurate, dynamic data analysis.

3. Power Query - Importing Data
This module covers:

• Importing various data types into Excel and consolidating them into a single workbook using Power Query to streamline analysis.
• The advantages of using Power Query over manual data entry, including time-saving and error reduction.
• Demonstrating the process of updating and refreshing imported data in Excel to ensure the analysis reflects the most current information.

4. Power Query - Cleaning Data
This module covers:

• The challenges of working with data that is inconsistently formatted or incorrect due to manual entry errors or system capture inconsistencies.
• Utilising Power Query in Excel to clean and standardise data, which involves transforming text entries, removing unwanted characters, and correcting errors to ensure uniformity.
• The importance of data validation and consistency for analysis, demonstrating the process of cleaning data to align with the desired format and saving the cleaned results for efficient future analysis.

5. Power Query - Appending Data
This module covers:

• Appending data from multiple Excel sheets into one to consolidate information.
• Removing duplicates after merging to ensure data accuracy.
• Renaming the query for clarity and easier future reference.

6. Power Query - Automating Formulae
This module covers:

• Automating Excel formulas within queries to enhance data analysis, with a focus on formula integration for efficiency.
• Step-by-step guidance on adding custom columns in Power Query for handling dates, and segmenting data into week, month, and year for simplified reporting.
• Techniques for calculating metrics such as speed of answer and talk time from call data, along with instructions for adjusting data types and formatting in the query editor.

7. Power Query - Combining Data

• This module covers:
• Merging data from different sheets into one using Power Query.
• Detailed instructions on combining data, with emphasis on correct matching and handling duplicates.
• Guidelines for final sheet management, including data protection and refresh options to maintain data integrity and relevance.
   

The learning is in bite sized chunks that you can watch and we watch at your own speed. With practical exercises you can complete as you go.
Then become an accredited by sharing how you have applied this learning in your own role.

To find out more and see if you have a compatible version of excel, watch the introduction video below.