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

Split, Group and Pivot Columns

Learn how to split text into separate columns, group rows to calculate summaries, pivot rows into columns, and unpivot columns into rows using Power Query Editor.

1. Introduction to Data Reshaping

Data reshaping changes the structure of a dataset without necessarily changing the meaning of its information. Power Query provides transformations that help organize data into a format suitable for reporting, modeling, and calculations.

Four Important Operations

  • Split Column: Divides one column into two or more columns.
  • Group By: Groups rows based on selected fields and calculates summary values.
  • Pivot Column: Converts distinct values from a column into separate columns.
  • Unpivot Columns: Converts multiple columns into attribute-value rows.
Split Column FullName → FirstName + LastName One column becomes multiple columns Group By Sales rows → Regional totals Many rows become summaries Pivot Column Month rows → Month columns Distinct values become headers Unpivot Columns Month columns → Month rows Wide data becomes long data

Visual 1: Four common ways to reshape and summarize data in Power Query.

2. Splitting a Column

Split Column is useful when a single field contains multiple pieces of information. You can separate values by a delimiter, character position, or another supported splitting rule.

Example: Split a Full Name

Before transformation

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. 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 split.
  4. Choose Split Column from the Transform or Home ribbon.
  5. Select By Delimiter or another suitable option.
  6. Choose the delimiter, such as a space, comma, or hyphen.
  7. Select the appropriate splitting option and confirm.
  8. Rename the resulting columns and inspect the data.
FullName Samiksha Patil Omkar Joshi Split FirstName LastName Samiksha Patil Omkar Joshi Review names containing middle names or missing delimiters.

Visual 2: Splitting a text column creates separate fields for analysis.

Important: A simple split may not handle every name correctly. Review rows with middle names, multiple spaces, or missing delimiters before using the results.

3. Group By in Power Query

Group By combines rows that share the same value in one or more selected columns. You can calculate totals, counts, averages, minimums, maximums, and other supported aggregations.

Example: Regional Sales Summary

Original sales data

OrderID Region Amount
101West2500
102North1800
103West3200
104North2200
105South1500

After Group By Region and Sum Amount

Region TotalSales
West5700
North4000
South1500

Steps to Use Group By

  1. Select the table in Power Query Editor.
  2. Select Home → Group By.
  3. Choose the column used to group rows, such as Region.
  4. Enter a name for the new aggregation column, such as TotalSales.
  5. Choose Sum as the operation.
  6. Select Amount as the column to aggregate.
  7. Confirm and inspect the summarized result.
Original Transactions West 2500 North 1800 West 3200 North 2200 Group By Regional Summary West 5700 North 4000 South 1500 Several transaction rows become one summary row per region.

Visual 3: Group By summarizes transactions by a selected field.

Common Group By Operations

Operation Purpose Example
SumAdds numeric values.Total sales by region.
Count RowsCounts rows in each group.Orders per customer.
AverageCalculates the mean of numeric values.Average order amount by region.
MinimumFinds the smallest value.Lowest sale per category.
MaximumFinds the largest value.Highest sale per product.
All RowsRetains a nested table for each group.Keep each region's detailed transactions together.

4. Pivot Columns

Pivot Column transforms distinct values from one column into separate column headings. It is often used to turn category or period values into a wider table.

Example: Monthly Sales by Region

Before pivoting

RegionMonthSales
WestJanuary5000
WestFebruary6200
NorthJanuary4300
NorthFebruary5100

After pivoting Month

RegionJanuaryFebruary
West50006200
North43005100

Steps to Pivot a Column

  1. Select the column containing values that should become new headings, such as Month.
  2. Select Transform → Pivot Column.
  3. Choose the values column, such as Sales, when prompted.
  4. Select the appropriate aggregation operation if required.
  5. Confirm and inspect the resulting table.
Important: If multiple rows exist for the same Region and Month combination, decide how the values should be aggregated. Sum is appropriate for additive sales values, but it may not be appropriate for every measure.

5. Unpivot Columns

Unpivot transforms selected columns into attribute-value rows. It is particularly useful when data is stored in a wide format, with each month, department, or category represented by a separate column.

Example: Quarterly Sales

Before unpivoting

RegionQ1Q2Q3
West120001500017000
North90001100012500

After unpivoting Q1, Q2, and Q3

RegionQuarterSales
WestQ112000
WestQ215000
WestQ317000
NorthQ19000
NorthQ211000
NorthQ312500
Wide Format West 12000 15000 North 9000 11000 Region + Q1 + Q2 Unpivot Long Format West Q1 12000 West Q2 15000 Region + Quarter + Sales Unpivot makes repeated period values easier to filter and analyze.

Visual 4: Unpivot converts multiple period columns into attribute-value rows.

Steps to Unpivot Columns

  1. Select the columns to unpivot, or select the identifier columns that should remain unchanged.
  2. Open the Transform tab.
  3. Choose Unpivot Columns or Unpivot Other Columns, depending on the selected fields.
  4. Review the generated Attribute and Value columns.
  5. Rename Attribute and Value to meaningful names, such as Quarter and Sales.
  6. Set suitable data types and inspect the result.
Best practice: Unpivoting period columns often creates a structure that is easier to filter, aggregate, and use with a date or period dimension.

6. Pivot vs. Unpivot

Feature Pivot Unpivot
Transformation Values become column headings. Columns become attribute-value rows.
Typical output Wider table. Longer table.
Example Month values become January and February columns. January and February columns become Month and Sales rows.
Common use Cross-tab summaries and comparison layouts. Preparing period-based data for analysis.

7. Common Mistakes and Troubleshooting

Split Column Creates Unexpected Results

Check the delimiter, split position, and number of delimiters. Inspect rows containing missing or repeated separators.

Group By Produces Incorrect Totals

Confirm that the correct numeric column and aggregation operation were selected. Check for duplicate records and incorrect data types.

Pivot Produces Errors

Review duplicate combinations of identifier and pivot values. Select an appropriate aggregation where multiple values exist.

Unpivot Creates Unexpected Null Values

Inspect blank source cells and determine whether they should remain null, be replaced, or be handled according to business rules.

New Values Do Not Appear as Expected

Review the source data and applied steps. Confirm that filters or earlier transformations have not removed the required records.

8. Practical Exercise

Exercise A: Split Customer Names

  1. Import a customer table containing a FullName column.
  2. Split FullName using a space delimiter.
  3. Rename the resulting columns FirstName and LastName.
  4. Inspect rows containing more than two name parts.

Exercise B: Group Sales by Region

  1. Import a sales transaction table.
  2. Open Group By and select Region.
  3. Calculate Sum of Amount and name it TotalSales.
  4. Compare the totals with the original transactions.

Exercise C: Unpivot Quarterly Data

  1. Import a table containing Region, Q1, Q2, and Q3.
  2. Keep Region as the identifier column.
  3. Unpivot Q1, Q2, and Q3.
  4. Rename Attribute to Quarter and Value to Sales.
  5. Check the resulting row count and data types.

Expected outcome: Three prepared datasets demonstrating text splitting, aggregation, and data reshaping.

9. Knowledge Check

Question 1: Which operation separates one text column into multiple columns?

A. Group By
B. Split Column
C. Append Queries
D. Merge Queries

Question 2: Which operation can calculate total sales for each region?

A. Group By
B. Split Column
C. Rename Column
D. Unpivot Columns

Question 3: What happens during a pivot transformation?

A. Rows are always deleted
B. Values from a column become separate columns
C. All data types become text
D. Queries are automatically published

Question 4: What does unpivoting generally do?

A. Converts selected columns into attribute-value rows
B. Deletes all null values
C. Combines unrelated tables automatically
D. Creates report themes

Question 5: Which operation is useful for calculating the average amount per region?

A. Replace Values
B. Split Column
C. Group By with Average
D. Rename Column

View Answers
  1. B — Split Column.
  2. A — Group By.
  3. B — Values from a column become separate columns.
  4. A — Converts selected columns into attribute-value rows.
  5. C — Group By with Average.

Summary

In this lesson, you learned how to split columns using delimiters, group rows to calculate summaries, pivot values into separate columns, and unpivot wide datasets into attribute-value rows.

You also explored practical examples, common transformation errors, and ways to reshape data into a clean and consistent structure for Power BI reporting.