INTRODUCTION FOR DATA ANALYSIS AND DASHBOARDING USING EXCEL
The Excel Power Query gets data from almost anywhere (web sites, databases, Excel files, SharePoint, Salesforce etc.) and enables you to manipulate it in many ways (clean, transform, merge and append) using a straightforward interface add-in to Microsoft Excel. Non-technical Excel users can access large corporate databases to conduct analysis and produce reports.
TARGET AUDIENCE
Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyse and display data.
COURSE OBJECTIVES
At the end of this course, delegates will be able to:
- Create effective and professional reports
- Gather and transform data from multiple sources
- Discover and combine data in mashups
- Learn about data model creation
- Explore, analyse, and visualize data
COURSE CONTENT
- Module 1 Introduction of Power Query
- Providing examples of tasks before learning Power Query
- Benefits of Power Query to individual or an organization, on how does it save time and other resources
- Presentation of reports, charting or dashboards in a more efficient and easier way, compare to VBA, or SQL which involves programming.
- Module 2 Installation of Power Query
- Power Query or Get & Transform in different version of Microsoft Excel and how to install or troubleshoot for installation failures.
- Module 3 Understanding Data Sources
- Common Data Import Sources
- Working with Data Sources from Worksheets, Web, CSV and Text
- Working with Folders and Multiple Files
- Module 4 The Query Editor
- An overview of Query Editor
- Module 5 Data Loading & Refreshing Queries
- Example of Data Loading from Excel Data
- Refreshing Queries when Source File Changes
- Module 6 Working with Useful Tip from Power Query
- Replace Values
- Filter Records
- Import Data from Text
- Create Index Columns
- Keep Duplicate Records
- Unpivot Data Using Excel Power Query
- Group Rows and Get Counts
- Getting Started with M
- Exercises and Quiz
