Course Duration – 1 day
Course Subjects:-
Overview
- Power Query Editor and basic transformations
- Quick insights and data quality & distribution
- Connecting to multiple data sources
- Load data into Excel from Multiple Sources such as SharePoint, csv, Web, and even Email
- Refresh Data and Power Query Refresh options
Common Transformations
- Split Columns (e.g. by delimiter, no. of characters, positions, digit to non-digit, etc.)
- Format Columns (e.g. UPPERCASE, lowercase, Capitalize Each Word, Trim, Clean, etc.)
- Statistic Functions (Sum, Minimum, Maximum, Medium, Average, Standard Deviation, Count Distinct Values)
- Replace Values
- Rounding data
- Filtering columns (Number filters and Text filters)
- Spot duplicates between two columns
- Understand data types (and why are they so important when dealing with data)
- Fill and Replace Values
- Finding and Correcting Errors in Data
Pivoting and transposing data
- Pivoting and unpivoting columns
- Transpose columns
Powerful Transformations
- Conditional Columns in Power Query (equivalent of IF and NESTED IF statements – but much easier to create/amend)
- Merge Data with another File (equivalent and much more efficient than a VLOOKUP)
- Column from Example (an even more powerful version of Flash Fill)
- Appending data between different files
- Combining all files in a folder
- Extract Text Based on Pattern
- Fuzzy matching
- Group By (grouping sets of data by a certain factor)
- Power Query Navigation Tips
- Finding and Correcting Errors in Data
- Create Custom Columns
- Excel’s Data Model (used in Pivot Tables to give them extra power e.g. combine different sources to create a bespoke Pivot Table)
Time Transformations
- Date & Time Transformations (building calendars , working out age, work out time and date differences, duration, etc.)
Automation
- Applied Steps
- Refresh options
