Faysal AzizFA-0472Data & Analytics
Course Outline on Analytics and Workflow
Introduction
Why this course
This course is designed for users who are comfortable using MS Excel and would like to enhance their skills using Excel for analytics and data acquisition. The tools and software system that we shall be using for this course is:
- MS Excel
- MS Power Query
- MS Automate (Desktop)
Learning Outcome:
By the end of this course, the learner may be expected to perform / comprehend:
- Import Export Data with Power Query
- Transform Data with Power Query
- Perform Date / Time Operations
- Perform Complex Data Analysis problems with Merge
- Understand M Formula Language
- Create Custom Functions
- Invoke Custom Functions
- Understand when to use Power Automate
- Flow Management
Prerequisites
Prerequisites
This is a very technical course the and learner needs to be have / be able to:
- Experience in using MS Excel
- Understanding of how to perform data transformation and pivoting using Excel
- Understand Windows filing systems and UI
- Basic statistical mathematics
- Exposure to DAX
Training outline
15 modules
·
01Introduction3 topics
- Overview
- Hands-on
- Integration
02Data Sets7 topics
- Data Acquisition
- Transformation
- Update and Refresh
- Pattern
- Merge
- Pivot
- Alternative backup methodologies
03Common Techniques5 topics
- Excel
- Managing updates to data
- Data Types
- Debugging
- Delete, Manage, Copy Queries & Backup Results
04Transformations9 topics
- Advanced Column Mergers
- Sorting
- Transformations and Data Manipulation
- Type Manipulation
- Grouping
- Aggregation
- Unpivot
- Pivot
- Conditions
05Date / Time1 topics
- D/T Exercises
06Custom Column and BAsic Manipulation2 topics
- Custom Columns Type Compatibility & Intrinsic Functions
- Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
07Combining and Appending Data3 topics
- Combine All Files in a Folder
- Combine All Sheets in a File
- Consolidation of Data from Multiple Sheets in the Current Workbook
08Functions3 topics
- Unpivot & Consolidate Data From Multiple Sheets (with Custom Function)
- Nesting
- Running Totals
09Automating tasks6 topics
- Understanding Automation process
- Implementation Methodologies
- Variable, Truncate Number, Get Random Numbers
- Create New List, Add Item to List, Remove Item From List, Clear List
- How to Work with Remove Duplicate items from List, Reverse List, Shuffle List
- How to Work with Merge Lists, Subtract Lists, Find Common List items
10Conditions5 topics
- Learn Conditional Actions
- How to work with Conditional Actions (Practical Use case)
- Working with Conditional Actions (If and Else)
- If , Else If, Switch, Case, Default Case
- Working with If File / Folder Exists and If Process Conditional Actions
11Loops2 topics
- Loop, Loop Condition, Next loop & Exit loop
- For Each loop
12DB3 topics
- Open and Close SQL Connection
- Execute SQL Statement
- DB with Stored Procedure
13Integration9 topics
- Launch Excel, Read from Excel, Close Excel
- Write to Excel Worksheet
- Insert Column , Insert Row to Excel Worksheet
- How to Add New Worksheet\
- How to Set Active Excel Worksheet
- How to "Get Active Excel Worksheet"
- How to "Get All Excel Worksheets"
- How to work with "Rename Excel Worksheet"
- How to work with "Delete Excel Worksheet"
14File System6 topics
- Work with Files Actions
- Read Text from file and Write to Text File
- Get Filepath Part Action
- Read from CSV file and Write to CSV File
- Work with Folder Actions
- Zip and Unzip Files
15Assesment0 topics
A programme built around your team.
Share your training goals and requirements.