Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Power Query Applied Steps

Learn how Power Query records transformation operations, manages Applied Steps, handles dependencies, and helps you troubleshoot data preparation problems.

1. Introduction to Applied Steps

Applied Steps is a list of operations recorded by Power Query when you transform data. Each operation normally creates a step that describes a change made to the query.

Examples include promoting headers, changing data types, removing columns, filtering rows, renaming columns, and creating calculated columns. Power Query uses these steps as a repeatable transformation sequence.

Why Are Applied Steps Important?

  • They make data preparation easier to review.
  • They record the sequence of transformation operations.
  • They allow you to inspect intermediate results.
  • They help identify where a transformation error occurs.
  • They can be executed again when the query is refreshed.
Source Sales Data Step 1 Headers Step 2 Change Type Step 3 Filter Result Clean Data Each step transforms the result of the previous step. Refreshing the query runs the transformation sequence again.

Visual 1: A typical sequence of Applied Steps.

2. Where to Find Applied Steps

  1. Open Power BI Desktop.
  2. Select Home > Transform data.
  3. Wait for Power Query Editor to open.
  4. Select a query from the Queries pane.
  5. Find the Query Settings pane.
  6. Review the list under Applied Steps.
Power Query Editor Queries Sales Products Customers Data Preview Product Quantity Revenue Laptop 5 2500 Monitor 8 1600 Query Settings PROPERTIES Name: Sales APPLIED STEPS Source Changed Type Filtered Rows Renamed Columns

Visual 2: Simplified layout showing the Applied Steps pane.

Tip: If Query Settings is not visible, check the View tab in Power Query Editor and enable the Query Settings pane.

3. Common Applied Steps

Step Name Purpose Example
Source Connects to the original data source. Connect to an Excel workbook.
Navigation Selects a table, worksheet, or other source object. Select the Sales worksheet.
Promoted Headers Uses the first row as column names. Column1 becomes Product.
Changed Type Sets the data type of selected columns. Revenue becomes Decimal Number.
Filtered Rows Keeps rows matching specified conditions. Keep records for the current year.
Removed Columns Removes unnecessary columns. Remove an unused comment field.
Renamed Columns Changes column names. CustName becomes Customer Name.
Added Custom Creates a column using a formula. Calculate Quantity multiplied by UnitPrice.
APPLIED STEPS Source Promoted Headers Changed Type Filtered Rows Renamed Columns Select a step to inspect the intermediate data preview.

Visual 3: Selecting a step displays the data at that stage.

4. Inspect and Edit Applied Steps

Selecting a step allows you to inspect the data produced by that operation. Some steps also provide a settings gear that lets you modify the transformation.

  1. Select the required query.
  2. Select a step in Applied Steps.
  3. Review the data preview.
  4. If a settings gear is available, select it.
  5. Modify the required options.
  6. Confirm the change and inspect the result.
1. Select Choose a step 2. Inspect Review preview 3. Modify Change settings 4. Verify Check output Not every step has an editable settings dialog. Always verify downstream steps after making a change.

Visual 4: A reliable workflow for reviewing transformation steps.

Best practice: Make one change at a time and inspect the result before continuing to the next operation.

5. Rename, Delete, and Reorder Steps

Rename a Step

Use a meaningful step name to make complex queries easier to understand. For example, a step named Keep Valid Orders can be clearer than a generic name such as Filtered Rows.

Delete a Step

Use the step's context menu or available delete control. Before deleting a step, check whether later steps depend on the output it creates.

Reorder a Step

Reordering can change the meaning of a query. A step that refers to a column created earlier may fail if moved before the column-creation operation.

Step Dependencies Create Revenue Rename Revenue Filter Revenue The filter depends on the column created by earlier steps. Review dependencies before deleting or moving a step.

Visual 5: Later operations may depend on earlier steps.

Warning: Deleting or moving steps can cause errors in subsequent operations. Review the preview from the changed step onward before loading the data.

6. Understanding Step Errors

Problem Possible Cause Suggested Solution
Column not found A column was renamed or removed earlier. Check the column references in the affected step.
Data conversion error Values do not match the selected data type. Inspect invalid values and select the correct type.
Unexpected row count A filter or grouping operation changed the result. Review the relevant step and its conditions.
Source error The source path, permissions, or source structure changed. Check the connection and source availability.
Expression error A formula references invalid names or values. Review the expression and its input columns.
Troubleshooting tip: Inspect the steps in order. Find the earliest step where the output becomes incorrect and investigate that operation first.

7. Understanding Power Query M Code

Power Query uses the M language to describe data connections and transformation operations. You can often inspect the generated expression by selecting a step or opening the Advanced Editor.

The following example demonstrates the general structure of an M query. The exact step names and expressions depend on your source data and transformations.

let Source = Excel.Workbook( File.Contents("C:\Data\Sales.xlsx"), null, true ), SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data], PromotedHeaders = Table.PromoteHeaders( SalesSheet, [PromoteAllScalars=true] ), ChangedType = Table.TransformColumnTypes( PromotedHeaders, { {"Product", type text}, {"Quantity", Int64.Type}, {"UnitPrice", type number} } ) in ChangedType
Note: This is an illustrative example. Replace the file path and source navigation details with values that match your own workbook. The worksheet name and column names must exist in the source.

8. Practical Exercise: Sales Data

Assume your sales dataset contains Product, Region, Quantity, UnitPrice, and OrderDate. Create a sequence of transformations to prepare the dataset for reporting.

Practice Tasks

  1. Connect to the sales data source.
  2. Promote the first row to headers if required.
  3. Set Quantity to Whole Number.
  4. Set UnitPrice to Decimal Number.
  5. Remove rows without a valid product.
  6. Add a custom column named TotalRevenue.
  7. Use Quantity multiplied by UnitPrice as the formula.
  8. Rename steps where useful.
  9. Select each step and inspect the data preview.
  10. Resolve any errors before loading the data.

Expected Calculation

Product Quantity UnitPrice TotalRevenue
Laptop 4 125.50 502.00
Monitor 3 200.00 600.00
Keyboard 5 25.00 125.00

Confirm that the TotalRevenue values are numeric and that the Applied Steps list reflects the transformation sequence.

9. Knowledge Check

Question 1: What is the purpose of Applied Steps?

A. To change report themes

B. To record query transformations

C. To publish reports

D. To create dashboard pages


Question 2: What should you do after editing a step?

A. Delete the source file

B. Ignore later steps

C. Inspect the output and verify dependent steps

D. Close Power BI immediately


Question 3: Why can reordering steps cause errors?

A. Later steps may depend on earlier transformations

B. It always changes the report theme

C. It removes every data source

D. It changes the operating system


Question 4: What is a useful way to investigate a query error?

A. Delete every step

B. Publish without checking

C. Inspect the earliest step where the output becomes incorrect

D. Change all columns to text

View Answers
  1. B — To record query transformations.
  2. C — Inspect the output and verify dependent steps.
  3. A — Later steps may depend on earlier transformations.
  4. C — Inspect the earliest incorrect step.

Summary

Applied Steps records the sequence of Power Query transformations. Selecting a step helps you inspect intermediate data. Some steps can be edited, renamed, deleted, or reordered. Changing one step may affect later operations. M code describes the underlying transformation logic. Always validate the final result before loading the data.

You have completed the lesson on Power Query Applied Steps. Continue practicing with real datasets to improve your transformation and troubleshooting skills.