New Excel - Data Cleanup and Power Query

Data cleaning in Excel

• Why do I need to clean data?
• What does well-prepared data look like?
• Difference between type and format
• Reference to data in other workbooks
• Problems with external references

Scanning data with Power Query

• From file
• From current workbook
• From website

The most common data cleansing and transformation operations

• Structural transformations
• Quality clean-ups and repairs
• Distribution modifications and calculations

Table relations management

• Merging
• Adding
• Descartes series of tables (all possible combinations of two tables)

Loading options for data tables

Using tables

• The role of Tables
• Functions of Tables
• Table references and formulas
• Using table ranges in functions

Creating a table-based dashboard

Intermediate Excel skills, such as using complex, nested, conditional, multi-conditional functions.

The course also includes data cleansing, branching and writing multiple nested functions, for which knowledge of the above and/or previous intermediate Excel knowledge, if not essential, is strongly recommended.