Overview
Power Query is an essential part of Power BI that helps data analysts connect to different data sources, clean information, transform datasets, and prepare data for reporting and analysis.
In this module, learners will explore how to retrieve data from files, online services, and Microsoft Fabric. They will develop practical skills in validating data, combining multiple datasets, creating summary tables, restructuring operational data, and automating recurring data preparation tasks.
The module focuses on real-world business scenarios involving Excel reports, PDF invoices, timesheets, operational records, and other enterprise data sources. Learners will understand how to build consistent and repeatable data preparation workflows using Power Query.
What Will Be Covered
2.1 Get Started with Power Query
Introduction to Power Query and its role in Power BI.
Understanding the Power Query Editor interface.
Exploring queries, steps, and transformation options.
Understanding the data preparation workflow.
Learning how Power Query transforms raw data into structured datasets.
2.2 Get Data from File Sources – Excel Tables, PDF Invoices and Others
Connecting Power BI to Excel workbooks and tables.
Importing data from PDF documents, including invoices and reports.
Understanding available file-based data connectors.
Selecting and navigating data within files.
Handling file structures and preparing imported information for transformation.
2.3 Get Data from Online Sources – SharePoint, Outlook, Dynamics 365, Web APIs and Default Connectors
Connecting to SharePoint data sources.
Retrieving data from Outlook and supported online services.
Connecting to Dynamics 365.
Understanding Web API connections.
Exploring standard Power Query connectors.
Selecting appropriate connectors based on business requirements.
2.4 Get Data from Fabric Warehouse – Dataflow
Understanding data connectivity with Microsoft Fabric.
Connecting Power BI to Fabric Warehouse through Dataflows.
Retrieving enterprise data for reporting and analysis.
Understanding how Fabric-based data sources fit into the Power BI data preparation workflow.
2.5 Data Access, Path and Permission Settings in Power Query
Understanding data source paths and connection settings.
Configuring data access and permissions.
Managing data source credentials.
Understanding privacy-level settings.
Identifying and resolving common access and connection issues.
2.6 Data Validation Steps and Basic Statistics
Understanding the importance of data validation.
Inspecting datasets using column quality, column distribution, and column profile.
Identifying null values, missing records, and inconsistencies.
Reviewing basic statistics to understand data characteristics.
Identifying data quality issues before proceeding with transformations.
Applying appropriate validation and cleaning steps.
2.7 Automate Daily Reports, Timesheets and Invoices Using Append and Combine Options
Understanding the Append and Combine operations.
Combining multiple daily, weekly, or monthly reports.
Consolidating timesheets from different employees or departments.
Combining invoice records from multiple files.
Handling similar datasets received through recurring reporting processes.
Preparing consolidated datasets for further analysis.
2.8 Prepare Summary Tables and Aggregates
Understanding the purpose of summary tables.
Grouping records based on selected columns.
Calculating totals, counts, averages, and other aggregations.
Summarizing operational and financial information.
Preparing aggregated datasets for management reporting.
2.9 Reshape Your Operational Data Using Pivot and Unpivot
Understanding when to pivot and unpivot data.
Converting rows into columns and columns into rows.
Restructuring operational datasets for reporting.
Preparing cross-tabulated and transaction-based information.
Handling data layouts that are not suitable for direct analysis.
2.10 Transform Tables and Generic Columns
Modifying table structures to meet reporting requirements.
Transforming generic columns into meaningful data fields.
Renaming and reorganizing columns.
Applying appropriate data types and formatting.
Removing unnecessary rows and columns.
Preparing structured tables for downstream analysis.
2.11 Derive KPIs and Flags Using Conditional Columns and Custom Columns
Creating conditional columns using business rules.
Developing custom columns for specific reporting requirements.
Applying conditions to classify records.
Deriving business flags and categories.
Creating calculated fields to support KPI reporting.
Transforming raw values into meaningful business indicators.
2.12 Create a Reporting Workflow and Enable Process Automation Using Applied Steps
Understanding Applied Steps in Power Query.
Recording and managing transformation steps.
Reviewing and modifying existing steps.
Building repeatable data preparation workflows.
Understanding how transformation steps are applied when source data is refreshed.
Managing the sequence of operations for recurring reporting processes.
2.13 Automate Lookup, Filtering and Reconciliation Processes Using Merge Query (SQL Joins)
Understanding the Merge Queries operation.
Matching records across different datasets.
Applying join operations to retrieve related information.
Understanding common SQL-style joins, including Left Outer, Right Outer, Inner, Full Outer, and Anti joins.
Performing lookup operations using common fields.
Filtering datasets based on matching and non-matching records.
Reconciling information across invoices, transactions, and operational reports.
2.14 Apply Logical Transformations on Values
Understanding logical transformations in Power Query.
Applying conditional logic to data values.
Transforming values based on specified conditions.
Handling business-specific classifications and exceptions.
Applying logical rules to prepare data for reporting.
2.15 Query Settings, Selective Load and Performance Troubleshooting
Understanding query settings and properties.
Managing query load options.
Configuring selective loading of tables and queries.
Understanding query dependencies and transformation sequences.
Identifying common query performance issues.
Troubleshooting data loading and transformation problems.
Improving the efficiency of data preparation workflows.
2.16 Case Studies – Practice with Real Business Data Scenarios
Learners will apply the concepts covered in this module to practical business datasets and reporting scenarios.
Importing and preparing business data from multiple sources.
Consolidating daily reports, timesheets, and invoices.
Validating and cleaning operational datasets.
Creating summary tables for reporting.
Restructuring data to meet reporting requirements.
Developing conditional columns and business flags.
Performing lookup and reconciliation activities.
Building repeatable data preparation workflows.
Through these exercises, learners will understand how to retrieve data from the correct sources, maintain data consistency, and prepare reliable datasets for reporting. Getting these fundamentals right makes reporting faster and more reliable.
Skills You Will Gain
By completing this module, learners will develop practical skills in the following areas:
1. Enterprise Data Connectivity
Connect Power BI to file-based and online data sources.
Retrieve information from Excel, PDF, SharePoint, Outlook, Dynamics 365, and Web APIs.
Connect to Microsoft Fabric Warehouse through Dataflows.
Manage data source paths, credentials, and permissions.
2. Data Validation and Preparation
Inspect datasets using Power Query profiling tools.
Identify missing values, inconsistencies, and data quality issues.
Validate data using basic statistics.
Prepare datasets through appropriate cleaning and transformation steps.
3. Data Consolidation and Restructuring
Append and combine multiple datasets.
Consolidate recurring reports, timesheets, and invoices.
Create summary tables and aggregated datasets.
Restructure information using pivot and unpivot operations.
Transform generic columns and table structures.
4. Business Logic and Data Transformation
Create conditional and custom columns.
Derive business KPIs, classifications, and flags.
Apply logical transformations to values.
Use Merge Queries to perform lookups, filtering, and reconciliation.
5. Workflow Automation and Query Management
Build repeatable workflows using Applied Steps.
Manage query settings and selective loading.
Troubleshoot common query performance and loading issues.
Prepare consistent datasets for recurring business reporting.
Learning Outcomes
By the end of this module, learners should be able to:
Connect Power BI to different enterprise data sources using Power Query.
Retrieve information from Excel files, PDF documents, SharePoint, Outlook, Dynamics 365, Web APIs, and Fabric Warehouse through Dataflows.
Configure data access, source paths, credentials, and permissions.
Validate datasets and identify data quality issues using profiling tools and basic statistics.
Consolidate daily reports, timesheets, and invoices using Append and Combine operations.
Create summary tables and aggregated datasets for business reporting.
Restructure operational datasets using Pivot and Unpivot.
Transform tables and generic columns into structured reporting formats.
Derive business KPIs and flags using Conditional Columns and Custom Columns.
Create repeatable data preparation workflows using Applied Steps.
Perform lookup, filtering, and reconciliation activities using Merge Queries and SQL-style joins.
Apply logical transformations to values based on business requirements.
Configure query settings, selective loading, and troubleshoot common performance issues.
Prepare consistent, structured, and analysis-ready datasets for Power BI data modelling.
Microsoft PL-300 Exam Alignment
This module primarily supports the Prepare the Data skill area of the Microsoft PL-300: Power BI Data Analyst examination.
Prepare the Data – 25–30% of the examination
Microsoft's study guide identifies three major competency groups within this area.
1. Get or Connect to Data
Candidates should be able to:
Identify and connect to different data sources.
Connect to shared semantic models.
Change data source settings, including credentials and privacy levels.
Choose between Import, DirectQuery, and DirectLake storage modes.
Create and modify parameters.
2. Profile and Clean the Data
Candidates should be able to:
Evaluate data using statistics and column properties.
Resolve inconsistencies and unexpected values.
Identify and resolve null values and data quality issues.
Troubleshoot data import errors.
3. Transform and Load the Data
Candidates should be able to:
Select appropriate column data types.
Create and transform columns.
Group and aggregate rows.
Pivot, unpivot, and transpose data.
Convert semi-structured data into tables.
Create fact and dimension tables.
Decide when to use reference or duplicate queries.
Merge and append queries.
Identify and create appropriate keys for relationships.
Configure data loading for queries.
PL-300 Preparation Checklist
Use this checklist to assess practical readiness for the Prepare the Data skill area.
Connect to different file-based and online data sources.
Configure data source credentials and privacy settings.
Understand Import, DirectQuery, and DirectLake storage modes.
Create and modify parameters.
Profile datasets using column quality, distribution, and statistics.
Identify and resolve null values and data inconsistencies.
Correct data types and troubleshoot import errors.
Create and transform columns.
Group and aggregate rows.
Pivot, unpivot, and transpose datasets.
Convert semi-structured data into tables.
Append and merge queries.
Understand query references and duplicates.
Prepare fact and dimension tables.
Identify suitable keys for data relationships.
Configure query loading and selective loading.
Important: The classroom curriculum provides practical coverage of many objectives within Prepare the Data. However, the PL-300 examination may assess additional capabilities beyond the specific classroom exercises. Learners should use the official Microsoft study guide and practise all applicable examination objectives.
Recommended Microsoft Learn Resources
The following official Microsoft Learn resources complement this module:
Prepare Data for Analysis with Power BI
Covers connecting to data sources, preparing and transforming information, and choosing an appropriate Power BI model framework.Get Data in Power BI
Learn how to connect to different data sources, configure storage modes, and troubleshoot data connection issues.Clean, Transform, and Load Data in Power BI
Practise data profiling, resolving inconsistencies, changing data types, shaping tables, and combining queries.Microsoft PL-300 Exam Study Guide
Review the complete examination objectives and identify the skills required under Prepare the Data.
Continue to Module 3: Data Modelling
Build on your data preparation skills by learning how to structure datasets, establish relationships, design effective data models, and prepare your Power BI solution for meaningful analysis.
Still need help?
Contact us