Unlocking Your Data Potential with Power Query
Are you ready to supercharge your data analysis skills and take your career to the next level? Look no further than the ‘Power Pivot & Power Query’ course, where we dive deep into the dynamic world of Power Query.
Unleash the Power of Data Transformation
In today’s data-driven business landscape, harnessing the full potential of your data is paramount. Power Query is your gateway to this transformation. In this course, you will learn how to seamlessly extract, transform, and load data from a variety of sources. From databases to Excel files, you’ll gain the skills to effortlessly prepare data for analysis, creating a solid foundation for decision-making.
Empower Your Career
Imagine the doors that will open for you with these invaluable skills. For sales executives, business analysts, project managers, and more, mastering Power Query can be a game-changer. Whether you’re looking to streamline your workflow, enhance your analytical capabilities, or simply stay ahead in the corporate world, this course is your ticket to success.
Don’t miss out on this opportunity to become a data wizard. Join us and become the go-to data expert in your organization, armed with the transformative power of Power Query.
Course Details
Course Code: -; Duration: 2 Days; Instructor-led
Overview
Power Pivot
- import data from multiple data sources into a single Excel workbook
- create relationships between heterogeneous data
- create calculated columns and measures using formulas
Power Query
- Import source from multiple data source
- Different methods to append (stack) data tables whether they are within the same file or spread across multiple source files
- A variety of transformation techniques and patterns to solve various data challenges
When to Use
Power Pivot
- Using large sets of data and need to compile multiple source data
- Need to manipulate data using formulas
- Automate data manipulation for future data
Power Query
- To extract only useful data from large and detailed data sets
- Cleanup data
- Automate the process of reshaping for future data
Audience
- Sales Executive / Managers
- Business Analysist
- Resource Planning Team
- Project Managers
- Purchasing Manager
- Financial Executive / Managers
Prerequisites
Basic knowledge of Windows is essential with the following pre-requisites:
- Able to switch between task applications
- Able to create a spreadsheet with simple formatting
- Able to use Auto Filter command
- Able to use basic functions – AutoSum, Count, Max, Min and Average functions
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion, and practical exercise.
Requirement
Power Pivot
- EXCEL 365
- Office Professional 2019
- Office Home & Business 2019
- Office Home & Student 2019
- Office 2016 Professional Plus (available via volume licensing only)
- Office 2013 Professional Plus
- Excel 2013 standalone
- Excel 2016 standalone
Power Query
- EXCEL 2016, 2019, 2021, 365 and
- EXCEL 2010 & 2013 Free Add-in : Download Add-In
Outlines
POWER QUERY
Lesson 1: Getting data from multiple of source
- Import data from Excel
- Import data Table / Range
- Import data from Text or CSV Files
- Import data from Web
- Import data from Folder
Lesson 2: Managing Query
- Duplicate Query
- Rename Query
- Switching Between Power Query Editor and Excel Screen
- Viewing Applied Steps in Query Settings pane
- Viewing details of Applied Steps in Formula Bar
- Renaming and Deleting Applied Steps
Lesson 3: Keep the Right Data
- Identify Column Header via Promote and Demote
- Changing Data Type
- Choose Columns
- Remove Blank Rows
- Removing Duplicate Records
- Remove Top/Bottom Rows
- Moving, Renaming Columns
- Reorganize data with Sorting
- Get relevant data with Filter
Lesson 4: Combine Table
- Joining multiple tables into master table via Append Queries
- Merging Queries on Single or Multiple Matching Columns
- Returning Referenced Details on Those Matched
- Returning Results on Those Not Matched
Lesson 5: Getting Results
- Close and Load data into Excel Table from Power Query
- Refresh query to get latest data from source
Lesson 6: Data Reshaping
- Replace Value
- Replace Error
- Transpose Rows & Columns
- Unpivoting Columns
- Pivoting Columns
- Splitting Column into Multiple Columns
- Merging Columns
- Fill empty cells with data above and below
Lesson 7: Output Different Result
- Manage number column
- standard calculation
- rounding
- Manage Text Column
- Converting Text to Uppercase, Lowercase
- Clean up non-printable characters with TRIM and CLEAN
- Adding Prefix / Suffix
- Extracting Part of Text
- Manage Date Column
- Calculate Age
- Extract Year, Quarter, Month & Day
- Summing Values based on Grouping
Lesson 8: Managing Additional Column
- Create Custom Columns
- Create Conditional Column
- Indexing Column
- Column from Examples
Lesson 9: Managing Data Source
- Data Source Settings
- Change Source Location
- Change to Different Source
POWER PIVOT
Lesson 1: Starting with PowerPivot
- Add-ins Power Pivot from Options
- Launch the PowerPivot window
- A Tour to PowerPivot window
- Exploring the PowerPivot tab
- Changing between Data & Diagram view
Lesson 2: Adding data to PowerPivot
- Get Data from database
- Get data from Excel files
- Get data from Text or CSV files
- Add Data by using Excel Linked Tables
- Load from Power Query
Lesson 3: Preparing data for analysis
- Working with Tables
- Managing Columns
- Set Data Type and Format
- Filtering and Sorting Data
Lesson 4: Manage Relationships & Hierarchy
- Understanding Types of Relationships
- Create a Relationship Between Tables
- View and Edit Relationships
- Delete Relationships
- Hierarchy
- Create & rename Hierarchy
- Adding Column to Hierarchy
- Remove Column from Hierarchy
Lesson 5: Manage Connection
- Refresh with latest data
- Editing connection
- Deleting connection
Lesson 6: Calculated Column
- Building Calculated Columns within a Table
- Building calculate Column across multiple table
Lesson 7: Measures
- Creating a New Measure
- Formatting a Measure
- Useful DAX Functions
- PREVIOUS MONTH / PREVIOUS QUARTER
- TOTALYTD / TOTALQTD / TOTALMTD
- SAMEPERIODLASTYEAR
- CALCULATE
- Modify and Delete Measures
Lesson 8: Key Performance Indicators (KPI’s)
- Create New KPI
- Manage KPI
Lesson 9: Date Table
- Create a Date Table
- Mark as Date Table
Lesson 10: Pivot Table & Chart
- Create a Pivot Table
- Adding Data into Pivot Table
- Formatting Pivot Table
- Report Layout
- Subtotal
- Grandtotal
- Pivot Table Style
- Create a Chart
- Adding Data in Chart
- Formatting Chart
- Style
- Elements
