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

Merge and Append Queries

Learn how to combine data from multiple tables using Merge Queries and Append Queries in Power Query Editor. Understand join types, matching columns, schema alignment, and common data combination errors.

1. Introduction to Merge and Append

Business data is often stored in multiple tables or files. Power Query provides two important operations for combining data: Merge Queries and Append Queries.

Merge Queries

Merge combines tables horizontally by matching values in one or more selected columns. It is commonly used when two tables contain related information about the same customers, products, orders, or other business entities.

Append Queries

Append combines tables vertically by adding rows from one table below the rows of another table. It is useful when datasets have the same or similar structure, such as monthly sales files.

MERGE QUERIES Orders OrderID, CustomerID Customers CustomerID, Name Combined Columns Order + Customer details APPEND QUERIES January Sales Rows from January February Sales More Rows in One Table

Visual 1: Merge adds related columns; Append adds rows from another table.

Remember: Merge is similar to joining tables. Append is similar to stacking compatible datasets vertically.

2. Understanding Merge Queries

Merge Queries combines two tables using matching column values. The selected columns act as keys for finding related rows. The selected join type determines which rows are retained.

Example: Orders and Customers

Orders table

OrderID CustomerID Amount
101C012500
102C021800
103C013200

Customers table

CustomerID CustomerName City
C01SamikshaPune
C02OmkarMumbai
C03PradnyaNashik

Both tables contain CustomerID. This shared field can be used to match each order to its customer details.

3. Steps to Merge Queries

  1. Open Power BI Desktop and select Home → Transform Data.
  2. Select the primary query, such as Orders.
  3. Open the Home tab in Power Query Editor.
  4. Select Merge Queries or Merge Queries as New.
  5. Select the second table, such as Customers.
  6. Select CustomerID in the first table and the corresponding CustomerID in the second table.
  7. Choose the required join kind.
  8. Confirm the merge operation.
  9. Use the expand icon in the resulting nested table column.
  10. Select the customer fields to include, such as CustomerName and City.
  11. Review the result and rename the query or columns if needed.
1. Select Orders Primary query 2. Select Customers Second query 3. Match Keys CustomerID 4. Choose Join Kind Left Outer, Inner, or another type 5. Expand Required Columns Inspect the merged result

Visual 2: A typical merge workflow in Power Query Editor.

4. Understanding Join Types

The join kind determines which records are retained when Power Query matches two tables. Choose the join according to the business question and the data that must be preserved.

Join Kind What It Returns Typical Use
Left Outer All rows from the first table and matching rows from the second. Keep every order and add customer details where available.
Right Outer All rows from the second table and matching rows from the first. Keep every customer from the second table.
Full Outer All rows from both tables, with matches combined. Identify records from either source, including unmatched records.
Inner Only rows that have matching keys in both tables. Keep only orders with matching customer records.
Left Anti Rows from the first table with no match in the second. Find orders whose customer key is missing from Customers.
Right Anti Rows from the second table with no match in the first. Find customers with no corresponding order record.
Left Table Right Table A Match B Left Outer All left rows + matching right rows Inner Only matching rows Full Outer All rows from both tables A = left-only records, B = right-only records, overlap = matches.

Visual 3: Join types control which matched and unmatched records are retained.

Important: If the selected key appears multiple times in the second table, a merge can create multiple output rows when the nested table is expanded. Check key uniqueness before merging.

5. Expanding Merged Columns

After merging, Power Query typically adds a column containing nested tables from the second query. Expand that column to select the fields you want to add to the result.

Example Result After a Left Outer Merge

OrderID CustomerID Amount CustomerName City
101C012500SamikshaPune
102C021800OmkarMumbai
103C013200SamikshaPune

CustomerName and City are added from the Customers table. Customer C01 appears twice because two orders belong to that customer.

  1. Locate the merged table column.
  2. Select the expand icon in the column heading.
  3. Choose only the fields needed for the report.
  4. Decide whether to keep or remove the original column-name prefix.
  5. Confirm and inspect the resulting table.

6. Understanding Append Queries

Append Queries adds rows from one query to another. It is useful when datasets represent the same type of information for different periods, branches, locations, or business units.

Example: Monthly Sales

JanuarySales

OrderIDProductAmount
101Laptop50000
102Monitor12000

FebruarySales

OrderIDProductAmount
103Keyboard2500
104Mouse900
JanuarySales 2 rows • OrderID 101, 102 FebruarySales 2 rows • OrderID 103, 104 CombinedSales January rows + February rows 4 total rows in this example Append aligns fields by column name, not simply by column position.

Visual 4: Appending monthly sales queries produces one longer sales table.

7. Steps to Append Queries

  1. Open Power Query Editor.
  2. Select Home → Append Queries or Append Queries as New.
  3. Choose whether to append two tables or three or more tables.
  4. Select the required queries.
  5. Confirm the operation.
  6. Review the resulting rows and columns.
  7. Check data types and values in the combined query.
  8. Rename the result to a meaningful name, such as CombinedSales.

What Happens When Column Names Differ?

Append aligns columns by column name. If one query contains Product and another contains ProductName, Power Query may create separate columns rather than recognizing them as the same field. Values that do not exist in a particular source may appear as null.

Important: Before appending, standardize column names and compatible data types. Otherwise, the combined result may contain unexpected columns, null values, or conversion errors.

8. Merge vs. Append: Key Differences

Feature Merge Queries Append Queries
Direction Combines related information horizontally. Combines records vertically.
Primary purpose Match related rows between tables. Add rows from one dataset to another.
Key requirement Usually uses one or more matching columns. Does not require matching keys.
Common example Add customer details to orders. Combine January and February sales.
Typical result Additional columns after expansion. Additional rows in the combined table.
What Do You Need to Combine? Identify the intended result Need More Columns? Match related rows Use MERGE Need More Rows? Stack compatible datasets Use APPEND Choose the operation based on the structure of the desired result.

Visual 5: Use the desired result to decide whether to merge or append.

9. Common Errors and Troubleshooting

Problem: Merge Produces Null Values

Possible causes include mismatched key values, leading or trailing spaces, different data types, or missing records. Compare the selected keys and standardize them before merging.

Problem: Merge Produces More Rows Than Expected

The matching key may occur multiple times in the second table. Check key uniqueness and decide whether duplicates are valid before expanding the merged column.

Problem: Append Creates Extra Columns

Column names may differ between the source queries. Standardize the schema and ensure that equivalent fields use consistent names.

Problem: Values Become Null After Append

A field may be missing from one source, or its name may differ. Inspect each input query and align the column names.

Problem: Data Type Errors Appear

Sources may contain different formats or invalid values. Check data types, locale settings, and conversion errors before loading.

10. Practical Exercise

Exercise A: Merge Orders with Customers

  1. Import an Orders table containing OrderID, CustomerID, and Amount.
  2. Import a Customers table containing CustomerID, CustomerName, and City.
  3. Open Power Query Editor.
  4. Merge Orders with Customers using CustomerID.
  5. Select Left Outer as the join kind.
  6. Expand CustomerName and City.
  7. Inspect the result for unmatched customer IDs.

Exercise B: Append Monthly Sales

  1. Import JanuarySales and FebruarySales.
  2. Ensure both queries have compatible column names and data types.
  3. Use Append Queries as New.
  4. Select both monthly queries.
  5. Name the new query CombinedSales.
  6. Verify that all expected rows are present.

Expected outcome: One query enriched with customer details and another query containing combined monthly sales records.

11. Knowledge Check

Question 1: Which operation combines related columns from two tables?

A. Append Queries
B. Merge Queries
C. Remove Rows
D. Change Type

Question 2: Which operation is suitable for combining January and February sales rows?

A. Merge Queries
B. Replace Values
C. Append Queries
D. Split Column

Question 3: What does a Left Outer join retain?

A. Only unmatched right rows
B. All left rows and matching right rows
C. Only matching rows
D. Only unmatched left rows

Question 4: Why can a merge create extra rows after expansion?

A. The report theme changed
B. The matching key appears multiple times in the second table
C. The query was renamed
D. The column font changed

Question 5: How does Append generally align columns?

A. By column name
B. By cell color
C. By report page order
D. By visual position

View Answers
  1. B — Merge Queries.
  2. C — Append Queries.
  3. B — All left rows and matching right rows.
  4. B — The matching key appears multiple times in the second table.
  5. A — By column name.

Summary

In this lesson, you learned how Merge Queries combines related tables using matching columns and how join types control the inclusion of matched and unmatched records.

You also learned how Append Queries combines rows from multiple datasets, why consistent column names and data types matter, and how to troubleshoot common data combination problems.