Learning Path
Business Intelligence
Level
Advanced
Enrollment
Closed
Instructor-Led Live Session
Live Session Recording
Expert Support
Case Studies & Projects
Dedicated Resource Portal
06 weeks (Sat & Sun)
08:30 PM to 10:30 PM
Language
Urdu / Hindi
Power Query is a self-service data extraction and manipulation tool in Power BI Desktop. This course will teach you how to use the Power Query User Interface as well as the components of the M language.
You will obtain a thorough understanding of Power Query’s building blocks, which include values, lists, records, tables, and functions.
This course offers a structured framework with numerous examples for performing a nearly infinite amount of data transformations that will improve your data model, speed-up report performance, and simplify your DAX.
There are five sections to the course:
1. Power Query Fundamentals
2. Simple Transformation Techniques
3. Advanced transformation and querying
4. Deep Dive into M Language
5. Data Modeling Fundamentals
Following each section, there are milestone projects that allow you to apply what you’ve learned in real-world scenarios. You will eventually create an interactive dashboard.
You can apply EVERYTHING you learn in this course to Microsoft Excel Power Query!
Participants will learn:
- Understanding the Power Query User Interface for Data Extraction, Transformation, and Loading
- Importing data from a variety of sources
- Using a range of transformation strategies and patterns to improve solutions.
- Organizing queries to manage complexity
- Designing a user-friendly data model.
- Debugging methods for Power Query solutions
- Appending data tables from the same or different source files
- Merging tables identify exact matches and mismatches, as well as approximate or “fuzzy” matches.
- Creating queries using Advanced Editor
- Using and creating custom Parameters and Functions
- Creating calendar tables for dimensional models in Power Query
- Tips for optimizing queries
What do you get in the training?
- Exercise and Project files
- Quizzes and Homework assignments
- Pre and Post skill assessment
- 1-month access to the resource portal
- HD Recording of all live sessions
Certificate of Completion
You will be awarded a course completion E-Certificate by TechTAR Solutions after fulfilling the course requirements.
- This course is designed for individuals who have little to no experience with the Power Query M Masterclass but work in a data-driven workplace. For example:
- Data analysts,
- Business intelligence professionals
- MIS Reporting specialists
- Finance Professionals etc.
- Anyone who wants to stay up to date with Microsoft’s incredible developments in Business Intelligence for today’s corporate world.
- Everyone who uses Excel or Power BI, and needs complete data control.
- Participants must complete the Power BI Essentials course before enrolling in this one.
- Experience with Microsoft Excel Formula & Functions and PivotTable is strongly recommended
- Classes will be interactive throughout & LIVE on Zoom. You would be able to ask questions & share views with the Technical Assistant, Trainer & class other participants.
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- 64bit version of Excel – 2016+
- Power BI Desktop
Section 1: Power Query Fundamentals
Query Editor Interface
- What is Power Query
- The Query Editor – Interface
- The Query Editor – Ribbons
- Add Column Tab Vs Transform Tab
- Enabling Formula Bar
- Understanding Advanced Editor Interface
- Applied Steps and its options
- Queries Pane and its options
- Saving the project
Defining Data Type
- Importance of Data Types
- Different Data Types
- Configure Automatic Data Type Detection
- Data Type Conversion Matrix
Data Profiling and Data Quality
- Understanding Data Preview Options
- Monospaced and Show Whitespace
- Data Profiling Tools
- Column quality
- Column Distribution
- Column Profile
- Interrogate Data Statistics
Getting Data From Different Sources
- Understanding Get Data User Experience
- Importing Basic Data
- Connecting to Delimited Flat Files
- Connecting to Data in Excel Workbooks
- Connecting to Data from Web
- Connecting Data in the Folder
- Organizing Queries in Folder
Understanding Parameters
- Parameter Interface
- Creating Dynamic Data Source
Query Editor Shortcuts and workflow
- Power Query Shortcuts
- How does Power Query work?
Section 2: Simple Transformation Techniques
Basic Row Transformation
- Promote Header Row
- Keep Rows
- Keep Top Rows
- Keep Bottom Rows
- Keep Range of Rows
- Keep Duplicates
- Keep Errors
- Remove Rows
- Remove Top Rows
- Remove Bottom Rows
- Remove Alternate Rows
- Remove Duplicates
- Removes Blank Rows
- Remove Errors
- Sort Values (Ascending / Descending)
- Filter Rows and Its Hidden Features
Basic Column Transformation
- Choose Columns
- Choose Columns
- Go to column
- Remove Columns
- Remove Columns
- Remove Other Columns
- Data Type and Detect Data Type
- Fill Up / Down
- Move column (Left, Right, To Begining, and To End)
- Replace Values
- Replace Values
- Replace Errors
- Adding Index Column
Basic Text Transformation
- Split Column
- By Delimiter
- By Number of Characters
- By Positions
- By Lower to Uppercase
- By Uppercase to Lowercase
- By Digit to Non-Digit
- By Non-Digit to Digit
- Splitting columns into Rows
- Format
- Lowercase
- Uppercase
- Capitalize Each Word
- Trim
- Clean
- Add Prefix
- Add Suffix
- Extract
- Lenght
- First Characters
- Last Characters
- Range
- Text Before Delimiter
- Text After Delimiter
- Text Between Delimiters
- Merge Columns
- Pro Tip: Merging Column from Transform Tab VS Merging Column from Add Column Tab
Basic Number Transformation
- Statistics Functions
- Sum
- Minimum
- Maximum
- Median
- Average
- Standard Deviation
- Count Values
- Count Distinct Values
- Standard
- Add
- Multiply
- Subtract
- Divide
- Integer-Divide
- Modulo
- Percentage
- Percent of
- Rounding
- Round Up
- Round Down
- Round
- Information
- Is Even
- Is Off
- Sign
Basic Date and Time Transformation
- Date
- Age
- Date Only
- Year
- Month
- Quarter
- Week
- Day
- Earliest
- Latest
- Time
- Time Only
- Hour
- Minutes
- Second
- Earliest
- Latest
- Duration
- Days
- Hours
- Minutes
- Seconds
- Total Years
- Total Days
- Total Hours
- Total Minutes
- Total Seconds
- Multiply
- Divide
- Statistics
- Understanding Locale
Basic Table Transformation
- Transpose
- Reverse Rows
Adding Columns
- Column from Example
- Conditional Column
- Understanding Conditional Column – M Code
Section 3: Advanced Transformation and Querying
Understanding Proper Dataset
- Data Structure
- 6 Speedy Steps for Data Preparation
Shape and Transform Tables
- Pivoting Column
- Unpivoting Column
- Unpivot Columns
- Unpivot Other Columns
- Unpivot only selected columns
- Grouping and Aggregating Data
- Custom Columns
Combining Queries (Append / Merge / Duplicate / Reference)
- Why Combining Queries?
- Appending Queries
- Append Criteria
- Appending individual queries
- Appending from Folder
- Merging Queries
- Merge Criteria
- 06 Join Types
- Cartesian Products
- Approximate Match
- Fuzzy Matching Basis
- Fuzzy Matching in Data Quality
- Duplicate Query Vs Reference Query
- Viewing Quey Dependencies
Configuring Query Load Destinations
- Enable Load
- Refreshing Queries
Query Error Auditing
- Query Error Auditing
- Query Diagnostics Tools
Section 4: Deep Dive into M Language
Building Blocks for M Language
- Introduction to building blocks in M
- Text functions in Power Query
- Date functions in Power Query
- Conditional Functions in Power Query
Power Query Objects
- List as object and List functions in Power Query
- Record as object and Record functions in Power Query
- Table & other objects and Table functions in Power Query
Parameters and Custom Functions
- Manual Creation of Custom Functions
- Debugging Custom Functions
- UI Assisted Parameters and Functions
Section 5: Data Modeling Fundamentals
Designing Data Model
- What is a Data Model?
- Data Tables vs. Lookup Tables
- Converting a Flat Table into Data Model
- Creating Table Relationships
- Relationship Cardinality & Filter Flow
- Active & Inactive Relationships
- Creating Star, Top-Down, and Snowflake Schemas
Optimize Model Performance
- Removing unnecessary rows and columns
- Reducing cardinality levels to improve performance
Individual Price
PKR 15,000/-
Per Person
Total charges for complete training
Group of Two
PKR 13,500
Per Person
Avail 10% off on a Group of two
Group of Three
PKR 12,750/-
Per Person
Avail 15% off on a Group of three
Group of Four or more
PKR 12,000/-
Per Person
Avail 20% off on a Group of four or more
Course Reviews
We have engaged TechTAR Solutions for a number of session in Excel and Power BI for our employees. Sohail Merchant is wonderful to deal with when making the bookings and the high level of experience the trainers hold is evident. We will definitely use them again in the future.!
I was tasked with seeking out a company to assist us in recreating our department’s appraisal Matrix and after meeting with TechTAR Solutions’ consultant, I knew they were the right fit to help get the job done! Their expertise and understanding of our needs made the whole process so simple.
Sohail Merchant from TechTAR Solutions’ [ explained the theory very well, also gave us sufficient room to ask questions and clarify our queries. Enough resources were provided to read and increase our knowledge on individual capacity..
Lots of useful information, Ideas & strategies with many practical examples. I learned a lot throughout the course & implementing it in my business. Sohail Merchant from TechTAR Solutions’ expertise gave me an amazing learning experience. Thank you.
The professionalism and Personalized service that we got from TechTAR Solutions was unlike any other company we worked with and they made us feel so comfortable by their natural communication and let’s do it attitude. Incredibly trustworthy and high value! Thankyou!
Please keep developing the products and solutions to make our lives more easier and work to be more focused efficient targeted and productive. Appreciate the contributions of the entire community in this.