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

Text and Date Cleaning

Learn how to clean text values, standardize inconsistent entries, correct date formats, handle invalid dates, and prepare reliable data using Power Query in Power BI.

1. Introduction to Text Cleaning

Text cleaning improves the consistency and quality of text fields. Data imported from different systems may contain extra spaces, inconsistent capitalization, spelling variations, or unwanted characters.

These inconsistencies can affect filtering, grouping, matching, and reporting. For example, "Pune", " pune ", and "PUNE" may need to be standardized when they represent the same city.

Common Text Problems

  • Leading and trailing spaces.
  • Repeated spaces between words.
  • Inconsistent uppercase and lowercase letters.
  • Unwanted characters or symbols.
  • Different spellings for the same category.
  • Empty strings and placeholder values.
City Values Before Cleaning Original Value Standardized Value " pune " "Pune" "PUNE" "Pune" "Pune " "Pune" Standardize values only when they have the same meaning.

Visual 1: Text cleaning helps standardize inconsistent values.

2. Trim, Clean, and Change Case

Power Query provides text transformation options that help standardize text fields. Select a text column and explore the Transform tab or the column context menu.

Operation Purpose Example
Trim Removes leading and trailing spaces. " Pune " becomes "Pune".
Clean Removes non-printable characters. Removes unwanted control characters.
UPPERCASE Converts text to uppercase. "Pune" becomes "PUNE".
lowercase Converts text to lowercase. "POWER BI" becomes "power bi".
Capitalize Each Word Capitalizes the first letter of each word. "samadhan patil" becomes "Samadhan Patil".
Replace Values Replaces specified text with another value. "Mum." becomes "Mumbai" when appropriate.
Raw Text " pune " Trim Remove spaces Case Standardize Result "Pune" Apply the transformations that match your data requirements.

Visual 2: A simple sequence for standardizing text.

Tip: Trim and Clean solve different problems. Trim removes leading and trailing spaces, while Clean removes non-printable characters.

3. Split and Standardize Text

Some columns contain multiple pieces of information in one field. Splitting a column can separate that information into more useful fields.

For example, a full name can be split into first and last names, or a product code can be separated using a delimiter such as a hyphen.

Example: Product Codes

Original value: ELEC-1025-BLK

  • Category: ELEC
  • Product ID: 1025
  • Color: BLK

In Power Query, select the column and choose Split Column. Select the appropriate delimiter and inspect the generated columns.

Split Column by Delimiter ELEC-1025-BLK ELEC 1025 BLK

Visual 3: A product code separated into three fields.

4. Introduction to Date Cleaning

Date values may be imported as text, interpreted using the wrong regional format, or contain invalid values. Standardizing date fields helps with sorting, filtering, time-based analysis, and relationships with calendar tables.

Source Value Potential Issue Recommended Check
2026-10-22 Usually follows year-month-day notation. Confirm the column is interpreted as a date.
22/10/2026 Day-first date format. Use a suitable locale when converting text.
10/22/2026 Month-first date format. Confirm the source uses month-first notation.
31/02/2026 Invalid calendar date. Investigate or handle the conversion error.
Blank Missing date information. Determine whether the date is required.
Important: The text 03/04/2026 can mean March 4 or April 3 depending on the date convention. Confirm the source format instead of guessing.

5. Convert Text into a Date

  1. Open Power Query Editor.
  2. Select the date column.
  3. Review the current data type shown in the column heading.
  4. Open the data type menu.
  5. If needed, select Using Locale.
  6. Choose Date as the data type and select the locale matching the source format.
  7. Review the converted values and investigate any errors.
Date Conversion Workflow Date Text 22/10/2026 Choose Locale Day-first Convert Type Date Validate Check output Select a locale that matches the source date format. Inspect conversion errors before loading the data.

Visual 4: A process for converting date text correctly.

Tip: Use Change Type > Using Locale when Power Query cannot interpret date text correctly using the default regional settings.

6. Extract Date Parts

Once a column has a valid date data type, Power Query can extract useful date components. These can help with grouping, filtering, and preparing data for time-based analysis.

Operation Example Date Result
Year 22 October 2026 2026
Month 22 October 2026 10
Month Name 22 October 2026 October
Day 22 October 2026 22
Day of Week 22 October 2026 Depends on the selected date and weekday numbering.

How to Extract Date Parts

  1. Select a column with the Date data type.
  2. Open the Add Column or Transform tab.
  3. Choose Date and select the required component.
  4. Review the generated column.
  5. Rename the column if needed.

7. Practical Exercise

Exercise A: Clean Customer Text

  1. Import a customer table with CustomerName and City.
  2. Trim leading and trailing spaces.
  3. Clean unwanted non-printable characters.
  4. Standardize city names using a consistent naming rule.
  5. Check the result for unexpected changes.

Exercise B: Clean Order Dates

  1. Import a table containing OrderID and OrderDate.
  2. Inspect the original date format.
  3. Convert text dates using the correct locale.
  4. Identify invalid and missing dates.
  5. Extract Year and Month into separate columns.
  6. Validate the results before loading.

Expected outcome: Standardized text fields and valid date columns ready for reporting.

8. Knowledge Check

Question 1: What does Trim generally do?

A. Removes duplicate rows

B. Removes leading and trailing spaces

C. Changes text into a date

D. Deletes a query


Question 2: When should you use Clean?

A. To remove non-printable characters from text

B. To calculate sales totals

C. To create a relationship

D. To publish a report


Question 3: Why is locale important when converting dates?

A. It changes the report theme

B. It removes all null values

C. It helps interpret regional date formats correctly

D. It creates duplicate rows


Question 4: What should you do with an invalid date?

A. Assume the correct date without checking

B. Investigate the source and handle the error according to data rules

C. Convert every date to zero

D. Delete every row in the table

View Answers
  1. B — Removes leading and trailing spaces.
  2. A — Removes non-printable characters from text.
  3. C — Helps interpret regional date formats correctly.
  4. B — Investigate and apply the correct data-quality rule.

Summary

In this lesson, you learned how to standardize text using Trim, Clean, case transformations, and Replace Values. You also explored splitting text into separate columns.

You learned how to convert text into dates, use locale settings, handle invalid date values, and extract date components. Always verify transformed values before loading data into Power BI.