Course Outline on Analytics and Workflow

Course Outline on Analytics and Workflow

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:

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

Outline:

1. Introduction
   1. Overview
   2. Hands-on
   3. Integration
2. Data Sets
   1. Data Acquisition
   2. Transformation
   3. Update and Refresh
   4. Pattern
   5. Merge
   6. Pivot
   7. Alternative backup methodologies
3. Common Techniques
   1. Excel
   2. Managing updates to data
   3. Data Types
   4. Debugging
   5. Delete, Manage, Copy Queries & Backup Results
4. Transformations
   1. Advanced Column Mergers
   2. Sorting
   3. Transformations and Data Manipulation
   4. Type Manipulation
   5. Grouping
   6. Aggregation
   7. Unpivot
   8. Pivot
   9. Conditions
5. Date / Time
   1. D/T Exercises
6. Custom Column and BAsic Manipulation
   1. Custom Columns Type Compatibility & Intrinsic Functions
   2. Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
7. Combining and Appending Data
   1. Combine All Files in a Folder
   2. Combine All Sheets in a File
   3. Consolidation of Data from Multiple Sheets in the Current Workbook
8. Functions
   1. Unpivot & Consolidate Data From Multiple Sheets (with Custom Function)
   2. Nesting
   3. Running Totals
9. Automating tasks
   1. Understanding Automation process
   2. Implementation Methodologies
   3. Variable, Truncate Number, Get Random Numbers
   4. Create New List, Add Item to List, Remove Item From List, Clear List
   5. How to Work with Remove Duplicate items from List, Reverse List, Shuffle List
   6. How to Work with Merge Lists, Subtract Lists, Find Common List items
10. Conditions
    1. Learn Conditional Actions
    2. How to work with Conditional Actions (Practical Use case)
    3. Working with Conditional Actions (If and Else)
    4. If , Else If, Switch, Case, Default Case
    5. Working with If File / Folder Exists and If Process Conditional Actions
11. Loops
    1. Loop, Loop Condition, Next loop & Exit loop
    2. For Each loop
12. DB
    1. Open and Close SQL Connection
    2. Execute SQL Statement
    3. DB with Stored Procedure
13. Integration
    1. Launch Excel, Read from Excel, Close Excel
    2. Write to Excel Worksheet
    3. Insert Column , Insert Row to Excel Worksheet
    4. How to Add New Worksheet\
    5. How to Set Active Excel Worksheet
    6. How to "Get Active Excel Worksheet"
    7. How to "Get All Excel Worksheets"
    8. How to work with "Rename Excel Worksheet"
    9. How to work with "Delete Excel Worksheet"
14. File System
    1. Work with Files Actions
    2. Read Text from file and Write to Text File
    3. Get Filepath Part Action
    4. Read from CSV file and Write to CSV File
    5. Work with Folder Actions
    6. Zip and Unzip Files
15. Assesment