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

Data Types and Column Operations

Learn how to assign correct data types, rename columns, remove unnecessary fields, reorder columns, replace values, split text, and perform essential column operations in Power Query Editor.

1. Understanding Data Types

A data type defines how Power Query interprets the values stored in a column. Correct data types are important for calculations, date filtering, sorting, relationships, and report visuals.

Common Data Types in Power Query

Data Type Example Typical Purpose
Text Samiksha Names, categories, codes, and descriptions.
Whole Number 125 Counts and integer quantities.
Decimal Number 125.75 Measurements and values requiring decimal precision.
Fixed Decimal Number 125.50 Fixed-precision numeric scenarios, including many currency calculations.
Date 2026-10-22 Calendar dates without a time component.
Date/Time 2026-10-22 14:30 Values containing both date and time.
Time 14:30:00 Time of day.
True/False True Logical conditions and flags.
Text Customer Name Whole Number Quantity: 25 Decimal Number Price: 125.75 Date 2026-10-22 Date/Time Date and time True/False Active: True Choose a type that matches the meaning of the data. Correct types improve filtering, sorting, and calculations.

Visual 1: Different columns require different data types according to their purpose.

2. How to Change a Column Data Type

Power Query can automatically detect data types, but automatic detection may not always match your requirements. Review important columns and assign the appropriate type explicitly.

Steps to Change a Data Type

  1. Open Power BI Desktop and select Home → Transform Data.
  2. Select the required query in Power Query Editor.
  3. Select the column you want to change.
  4. Use the data type icon in the column header or the Data Type option on the ribbon.
  5. Choose the appropriate data type.
  6. If prompted, select the option to replace the existing type conversion or add a new conversion step.
  7. Inspect the preview for errors or unexpected values.
  8. Select Close & Apply when the data is ready.

Example: Correcting Sales Data Types

Column Incorrect Type Recommended Type
OrderDate Text Date
Quantity Text Whole Number
UnitPrice Text Decimal or fixed decimal, depending on requirements.
CustomerID Whole Number Text if used as an identifier, especially when leading zeros matter.
Important: Changing a data type may create errors if values cannot be converted. Dates and numbers may also require the correct locale when the source uses different formatting conventions.

3. Renaming Columns

Column names should be clear, consistent, and meaningful to report users. Names such as TotalSales, OrderDate, and ProductCategory are usually easier to understand than Column1 or Field3.

Steps to Rename a Column

  1. Open Power Query Editor.
  2. Locate the column that needs a better name.
  3. Double-click the column heading, or right-click it and select Rename.
  4. Enter the new column name.
  5. Press Enter and verify the result.
Before Renaming Column1 Field2 Column3 Rename After Renaming OrderDate ProductName TotalSales

Visual 2: Meaningful column names make data preparation and report maintenance easier.

Best practice: Use names that describe the business meaning of each field. Avoid unnecessary abbreviations and duplicate names.

4. Removing and Reordering Columns

A source dataset may contain many fields that are not needed for analysis. Removing irrelevant columns can simplify the query and make the dataset easier to maintain.

Removing Columns

  1. Select the column or columns that are not required.
  2. Right-click and select Remove Columns, or use the Remove Columns command on the ribbon.
  3. Review the remaining columns to ensure that important information has not been removed.

Reordering Columns

  1. Select a column heading.
  2. Drag it to the desired position, or use the available Move commands.
  3. Arrange the columns in a logical order for easier inspection.

Example: Simplifying an Orders Table

Original columns:

OrderID | CustomerName | OrderDate | InternalNote | Region | Amount

After removing an unused field and reordering:

OrderID | OrderDate | CustomerName | Region | Amount

Remove InternalNote only if it is genuinely unnecessary for reporting, auditing, or future analysis.

5. Replacing Values in a Column

Replace Values helps standardize known inconsistencies in a column. It is useful when the source contains alternate labels, outdated category names, or text values that should follow a consistent rule.

Steps to Replace Values

  1. Select the column that contains the values to correct.
  2. Right-click and select Replace Values, or use the appropriate ribbon command.
  3. Enter the value to find.
  4. Enter the replacement value.
  5. Confirm the operation and inspect the resulting values.
Original Value Replacement Reason
W West Standardize a known region code.
N North Use a consistent region label.
Unknown Unclassified Use an agreed business label where appropriate.
Important: Do not replace values blindly. Confirm the meaning of the original value first, especially when working with financial, operational, or compliance data.

6. Splitting a Column

Split Column divides the contents of one column into multiple columns. It can split text using a delimiter, a character position, or other supported rules.

Example: Split a Full Name

Original column:

FullName Samiksha Patil Omkar Joshi Pradnya Deshmukh

After splitting by a space delimiter:

FirstName LastName Samiksha Patil Omkar Joshi Pradnya Deshmukh

Steps to Split a Column

  1. Select the text column.
  2. Open the Transform or Home tab in Power Query Editor.
  3. Select Split Column.
  4. Choose By Delimiter or another appropriate split method.
  5. Choose the delimiter and splitting options.
  6. Confirm the operation and rename the resulting columns if needed.
  7. Check rows with missing delimiters or multiple delimiters.
Original Column Samiksha Patil Omkar Joshi Split FirstName LastName Samiksha Patil Omkar Joshi Review exceptions such as middle names or missing delimiters.

Visual 3: Splitting by a delimiter creates separate columns from text values.

7. Trimming, Cleaning, and Formatting Text

Text fields may contain extra spaces or non-printing characters that interfere with grouping and matching. Power Query provides text transformation commands to help normalize such values.

Operation Purpose Example
Trim Remove leading and trailing spaces. " West " becomes "West".
Clean Remove non-printing characters where supported. Remove unwanted control characters.
Uppercase Convert text to uppercase. "west" becomes "WEST".
Lowercase Convert text to lowercase. "WEST" becomes "west".
Capitalize Each Word Capitalize the first character of each word. "central region" becomes "Central Region".
Best practice: Apply text formatting consistently when it supports your reporting requirements. Do not change case or spacing if the original format carries business meaning.

8. Understanding Column Operations in Power Query

Column operations are used to reshape and prepare data. The correct operation depends on the structure of the source and the desired reporting result.

Operation What It Does Example Use
Rename Changes a column's label. Field1 to CustomerName.
Remove Deletes selected columns from the query output. Remove unused comments.
Duplicate Column Creates another column based on the selected column. Keep the original date and create a separate transformation.
Split Column Separates one column into multiple columns. Split a code into prefix and number.
Replace Values Replaces matching values according to the operation's rules. Standardize a known category.
Change Type Changes how values are interpreted. Convert a text amount into a numeric value.
Extract Creates a result from selected parts of a text value. Extract text before a delimiter.

9. Practical Exercise: Prepare an Orders Dataset

Scenario: An organization receives an orders file containing inconsistent names, text-formatted dates, and unnecessary fields.

Sample Data

OrderID | CustomerName | OrderDate | Region | Amount | InternalNote 1001 | Samiksha Patil | 22/10/2026 | West | 2500 | Check 1002 | Omkar Joshi | 23/10/2026 | North | 1800 | Old 1003 | Pradnya Deshmukh| 24/10/2026 | South | 3200 | Review

Tasks

  1. Import the dataset into Power BI Desktop.
  2. Open Power Query Editor.
  3. Rename the query to OrdersData.
  4. Remove InternalNote if it is not needed.
  5. Trim the Region column.
  6. Set OrderDate to Date using the correct locale if necessary.
  7. Set Amount to a suitable numeric type.
  8. Review CustomerName for inconsistent spacing.
  9. Rename any unclear column headings.
  10. Inspect Applied Steps and select Close & Apply.

Expected outcome: A consistently named and typed orders table that is ready for modeling and report development.

10. Knowledge Check

Question 1: Which data type is most appropriate for a calendar date without a time component?

A. Text only
B. Date
C. True/False
D. Binary

Question 2: Which operation removes leading and trailing spaces?

A. Merge
B. Append
C. Trim
D. Pivot

Question 3: Why might a customer ID need to be stored as text?

A. To make it a date
B. To preserve identifier formatting such as leading zeros
C. To convert it into a decimal
D. To sort it as a calendar

Question 4: Which operation separates a full name using a space delimiter?

A. Split Column
B. Remove Rows
C. Group By
D. Close & Apply

Question 5: What should you do after changing a column's data type?

A. Ignore conversion errors
B. Delete the query
C. Review the data preview for errors and unexpected values
D. Publish immediately without checking

View Answers
  1. B — Date.
  2. C — Trim.
  3. B — To preserve identifier formatting such as leading zeros.
  4. A — Split Column.
  5. C — Review the data preview for errors and unexpected values.

Summary

In this lesson, you learned how to select appropriate data types, rename columns, remove unnecessary fields, reorder columns, replace values, and split text in Power Query Editor.

You also explored text cleaning, practical column operations, and methods for validating transformation results before loading data into the Power BI model.