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
- 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
Pivot Tables & Chart
- Summarize data
- Visualize the data with chart, sparkline, icons
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
- To create data analysis using the 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 from Text or CSV Files
- Import data from Folder
Lesson 2: 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 3: 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 4: Getting Results
- Close and Load data into Excel Table from Power Query
- Refresh query to get latest data from source
Lesson 5: Data Reshaping
- Replace Value
- Unpivoting Columns
- Splitting Column into Multiple Columns
- Merging Columns
- Fill empty cells with data above and below
Lesson 6: Output Different Result
- Manage Text Column
- >Converting Text to Uppercase, Lowercase
- >Clean up non-printable characters with TRIM and CLEAN
- Manage Date Column
- >Calculate Age
- >Extract Year, Quarter, Month & Day
Lesson 7: Managing Additional Column
- Create Custom Columns
- Create Conditional Column
- Column from Examples
Lesson 8: Managing Data Source
- Data Source Settings
- Change Source Location
POWER PIVOT
Lesson 1: Starting with PowerPivot
- Add-ins Power Pivot from Options
- Launch the PowerPivot window
- Changing between Data & Diagram view
- Load from Power Query
Lesson 2: Manage Data, Relationships & Hierarchy
- Set Data Type and Format
- 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 3: Calculated Column & Measures
- Building Calculated Columns within a Table
- Building calculate Column across multiple table
- Creating a New Measure
- Formatting a Measure
- Modify and Delete Measures
Lesson 4: Date Table
- Create a Date Table
- Mark as Date Table
PIVOT TABLE & CHARTS
Lesson 1: Create & Quick Formatting Pivot Table
- Create Pivot Table from Data Model
- Configure Report Layout
- Setting Pivot Table Style
- Turn On summary with Subtotal and Grand total
- Navigating report using Expanding and Collapsing
Lesson 2: Extracting Meaningful Data
- Rearranging Top and Bottom value
- Enabling Multiple Filer
- Label Filter
- Value Filter
Lesson 3: Analyzing Pivot Table
- Changing Summary Value with different formula
- Measuring Value as Percentage
- Preparing Accumulated Value
- Comparing values against previous and next category
- Adding calculation for multiple value fields
Lesson 4: Getting Latest Data into Pivot Table Report
- Refreshing Pivot Table for additional data
Lesson 5: Sporting trends with Conditional Formatting
- Highlighting the Top 10 analysis
- Spotting larger or smaller numbers in a range
- Applying indicator icon to visualize range of value
Lesson 6: Showcase Meaningful data as Pivot Chart
- Getting the right chart to visualize the data
- Customizing Pivot Chart Layouts and style
- Joining two type of chart to represent 2 corresponding value
Lesson 7: Mini charts for series of data with Sparkline
- Show trends in a series of values with Sparkline – Line
- Highlight maximum or minimum values with Sparkline – Column
- Quick formatting multiple sparkline with ONE click
Lesson 8: Sketch Dashboard Layout
- Compiling all visual representation into one page
- Create best layout by position all items
- Standardize the Formatting of the page
- Linking important value from pivot table into Dashboard
Lesson 9: Interactive Selection with Slicer and Timeline
- Dynamic filter to update entire dashboard with slicer
- Focusing on a selected time period with Timeline
Lesson 10: Exporting Excel Dashboard into PowerPoint
- Linking Excel Dashboard in Power Point
- Setting interaction with OLE Action Verb
- Setting Up Presenter View
- Presenting Dashboard
