POWER QUERY and DATA CLEANSING for MS Excel Users
– a MASTER CLASS workshop
Introduction
Every day, we generate, send and receive data, only to spend so much time, energy and effort transforming the same into the right format before we begin to analyze it or incorporate it into reports. Precious time is spent on repetitive data cleansing tasks like:
- Deleting rows, columns, or blanks
- Filtering out data you don’t need
- Fixing the layout so it will work in Pivot Table
- Splitting or joining columns or text
- Removing spaces and special characters from your data
- Merging worksheets, text/CSV files together
- Extracting data from an external database
- Incorporating the next month’s data to an existing report, etc.
Objectives
At the end of the workshop, participants will be able to learn how best to:
- Transform messy data within minutes, making it ready for analysis
- Update and automate reports with the Refresh button
- Highlight and learn rich resources available in Power Query for data cleansing
- Learn to connect to external data sources
- Master M – Language easily with friendly GUI (Graphical User Interface)
Content
- What is Power Query?
- Where can I find a Power Query?
- Main components and Panes of Power Query?
- What are the Basic Data Challenges?
- The Data Connector Engine (Importing Data)
- Files: Excel, CSV, text, etc.
- Databases: Access, Oracle, Sybase, etc.
- Others: Web, MS Exchange, Facebook, etc.
- The ETL Engine (Extract, Transform & Load)
- Managing Tables, Rows & Columns: Add, Remove, Sort, Split, etc.
- Transform: Merge, Append, Group, Pivot, Unpivot, etc.
- Introduction to M – Programming Language
- The use of UI (User Interface)
- Editing in the formula bar
- M – Language functions
- Beyond the Basics
- Working with M – Language
- Advanced Editor
- Custom functions
Target Audience
- Analysts
- Data Analysts
- Business Analysts;
- Business Intelligence professionals;
- Financial Analysts;
- Finance
- Accountants
- Bankers
- Insurance
- Auditors
- Stockbrokers
- Sales and Marketing
- Marketers
- Sales professionals
- Brand and Product Managers
- Manufacturing
- Warehousing and Stock keeping
- Purchasing
- Production, Engineering, etc.
- HR and Personnel, Admin, Commercials, etc.
FEES:
One (1) week (40 Hours) N100,000.00
Two (2) weeks (80 Hours) N150,000.00
NOTES:
Interested participants to give a minimum of two (2) weeks’ notice
Executive/Private coaching is available. Call: +234 (0) 70 55 77 77 22
