Power Query and Data Cleansing

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 into an existing report, etc.

But with Power Query, you would be able to automate various tasks which would have taken long hours of work and be able to perform various complex data cleansing activities. Power Query also serves as a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed. Power Query is a powerful tool for modeling and shaping data

Learning Objectives

At the end of the workshop (Power query and Data Cleansing), 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)

Course Module

• What is Power Query?
  • Where can I find the 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.

Mode Of Delivery

Virtual Class with instructor-led class exercises and project-based/assignments learning methodology
18hours of learning sessions
Course material and Course Certificate will be issued
Course video available on demand
Face- to- Face learning available on request