Practicing Power Query, SQL, and Data Transformation Questions

Course: Microsoft Power BI Training

Topic: Practicing Power Query, SQL, and Data Transformation Questions

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power Query, SQL, and Data Transformation

Data transformation is the process of converting raw data into a clean, consistent, structured, and analysis-ready format. In Power BI projects, data often comes from Excel files, CSV files, relational databases, APIs, and business applications. These sources may contain duplicate records, missing values, inconsistent formats, incorrect data types, and unnecessary columns.

Power Query and SQL are two essential tools for preparing data. Power Query provides a visual environment for connecting to data sources and applying repeatable transformations. SQL enables analysts to retrieve, filter, join, aggregate, and manipulate data stored in relational databases.

Practicing questions about these tools helps learners develop practical data preparation skills, solve business problems, improve reporting accuracy, and prepare for Power BI and data analyst interviews.

1.1 Learning Objectives

• Understand the purpose and features of Power Query.
• Learn to clean, filter, sort, and transform datasets.
• Understand Applied Steps and the Power Query M language.
• Practice SQL queries for retrieving and manipulating data.
• Learn to use SQL joins, aggregate functions, and conditional expressions.
• Understand how to identify and handle null values, duplicates, and errors.
• Differentiate between Merge Queries and Append Queries.
• Understand query folding and transformation performance.
• Validate transformed data using row counts, keys, and aggregate totals.
• Solve practical questions commonly asked in technical interviews.

2. Understanding Power Query

Power Query is a data connectivity and transformation technology available in Microsoft Power BI and Excel. It allows users to import data from different sources, apply transformation steps, and load the prepared results into a destination.

2.1 Important Features of Power Query

2.2 Power Query Workflow

1. Connect to a data source.
2. Preview the source data.
3. Identify missing values, duplicates, and incorrect formats.
4. Remove unnecessary rows and columns.
5. Assign correct data types.
6. Apply filters and standardize values.
7. Merge or append queries when necessary.
8. Validate the transformed dataset.
9. Load the prepared data into Power BI.

2.3 Example of Raw Sales Data

This dataset contains two similar-looking Laptop records and a missing Quantity value. Before removing the repeated record, verify whether it represents a genuine duplicate transaction. The missing quantity should also be investigated before deciding how to handle it.

3. Power Query Editor and Applied Steps

The Power Query Editor provides the interface used to inspect and transform data before loading it into Power BI.

3.1 Main Components

3.2 What Are Applied Steps?

Applied Steps record the sequence of transformations performed on a query. Each step generally uses the result of a previous step to produce the next result.

For example, a query may contain these steps:

1. Source: Connects to the original data source.
2. Navigation: Selects a particular worksheet, table, or object.
3. Promoted Headers: Uses the first row as column headers.
4. Changed Type: Assigns appropriate data types.
5. Filtered Rows: Retains records matching specific conditions.
6. Removed Columns: Deletes unnecessary fields.

Practice question: What happens if a column is removed in an earlier step but is required in a later step?

Answer: The later step may return an error because the column no longer exists. Review the Applied Steps and adjust their order or update the affected column reference.

3.3 Why Is Step Order Important?

Transformation order can affect both the output and performance of a query. Removing unnecessary columns early may reduce processing, but removing a column before it is used in a calculation can break the query.

Always check dependencies between steps and validate the final output after changing their order.

4. Common Power Query Transformation Questions

4.1 How Do You Remove Duplicate Rows?

Power Query provides a Remove Duplicates command for eliminating repeated records based on selected columns.

1. Open Power Query Editor.
2. Select the relevant column or columns.
3. Choose Remove Rows and then Remove Duplicates.
4. Review the resulting row count.
5. Confirm that valid business transactions have not been removed.

Interview question: What is the difference between removing duplicates across all columns and removing duplicates using one column?

Answer: Removing duplicates across all columns compares complete rows. Selecting one column compares values in that column, even when other fields differ. The correct method depends on the business key and the intended level of detail.

4.2 How Do You Handle Null Values?

Null values represent missing or unavailable information. They should be handled according to the meaning of the field and the business requirements.

• Investigate why the value is missing.
• Retain nulls when the missing information is meaningful.
• Replace nulls with zero only when zero is a valid interpretation.
• Use a label such as "Unknown" when appropriate for categorical fields.
• Use a documented imputation method when estimating missing numerical values is justified.
• Remove rows only when doing so is appropriate and does not introduce unacceptable bias.

Example: A missing discount is not necessarily equivalent to a zero discount. Verify the source system's rules before replacing it.

4.3 How Do You Change Data Types?

Select the column and choose the appropriate data type, such as Text, Whole Number, Decimal Number, Date, or Date/Time.

Correct data types ensure that calculations, sorting, filtering, relationships, and date operations work as expected.

Example: An OrderDate column stored as text should be converted to Date or Date/Time, provided the source values can be interpreted correctly.

4.4 How Do You Split a Column?

The Split Column feature separates one column into multiple columns based on a delimiter, a fixed number of characters, or another supported rule.

For example, the value "Mumbai, Maharashtra" can be split using a comma delimiter.

1. Select the column.
2. Choose Split Column.
3. Select By Delimiter.
4. Choose the comma delimiter.
5. Rename the resulting columns to City and State.
6. Check for extra spaces and unexpected values.

4.5 How Do You Replace Inconsistent Values?

The Replace Values command changes matching values in a selected column. It is useful for correcting known inconsistencies and standardizing categories.

For example, "Mumbai", "mumbai", and "Mumbai " may refer to the same city. Trimming spaces and standardizing case can help, while spelling variations may require a mapping table.

4.6 How Do You Filter Rows?

Filtering retains rows that satisfy specific conditions.

For example, filtering Quantity to values greater than 10 keeps only records meeting that condition.

Always check the business requirement before excluding cancelled orders, returns, historical records, or other categories that may be needed for accurate reporting.

5. Power Query M Language Practice

M is the formula language used by Power Query to define data retrieval and transformation steps. Most transformations can be created through the graphical interface, but understanding M helps with advanced customization and troubleshooting.

5.1 Basic M Query Structure

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"OrderID", Int64.Type},
            {"Product", type text},
            {"Quantity", Int64.Type},
            {"Price", type number}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedType,
        each [Quantity] > 0
    )
in
    FilteredRows

Explanation:

• let begins the query's step definitions.
• Source references a table named Sales in the current Excel workbook.
• Table.TransformColumnTypes assigns data types.
• Table.SelectRows retains rows where Quantity is greater than zero.
• in specifies the final result returned by the query.

This example assumes the workbook contains a table named Sales with the specified columns. Null or invalid Quantity values should be handled according to the required business rules.

5.2 Removing Duplicate Rows Using M

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    UniqueRows = Table.Distinct(Source)
in
    UniqueRows

This example removes duplicate rows based on their row values. If uniqueness is defined by a business key, such as OrderID, use that key and confirm the rule for selecting the retained record.

5.3 Replacing Null Values Using M

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ReplacedNulls = Table.ReplaceValue(
        Source,
        null,
        0,
        Replacer.ReplaceValue,
        {"Quantity"}
    )
in
    ReplacedNulls

This replaces null Quantity values with zero. Use this transformation only when the business rules establish that zero is a suitable replacement.

5.4 Creating a Custom Column

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    AddedRevenue = Table.AddColumn(
        Source,
        "Revenue",
        each [Quantity] * [Price],
        type number
    )
in
    AddedRevenue

The Revenue column is calculated by multiplying Quantity by Price. If either input contains null or invalid values, define the appropriate handling logic before using the result in reports.

6. Introduction to SQL for Data Transformation

SQL stands for Structured Query Language. It is used to retrieve, filter, sort, aggregate, and manipulate information stored in relational databases.

6.1 Basic SQL Query Structure

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

Explanation:

• SELECT specifies the columns to retrieve.
• FROM specifies the source table.
• WHERE filters individual rows.
• ORDER BY sorts the results.

6.2 Sample Sales Table

Use this sample table to practice basic SQL operations. In the examples below, assume that the table is named Sales.

7. SQL Practice Questions and Answers

7.1 Retrieve All Records

Question: Display all records from the Sales table.

SELECT *
FROM Sales;

Answer explanation: The asterisk retrieves all columns. For production queries, explicitly selecting the required columns can improve clarity and reduce unnecessary data retrieval.

7.2 Filter Records Using WHERE

Question: Find sales records from Mumbai.

SELECT OrderID, Customer, Product, City
FROM Sales
WHERE City = 'Mumbai';

The WHERE clause returns only records satisfying the city condition.

7.3 Find Products Above a Specific Price

Question: Find products priced above 10,000.

SELECT OrderID, Product, Price
FROM Sales
WHERE Price > 10000;

The greater-than operator filters records using the Price column.

7.4 Sort Records

Question: Display products from highest to lowest price.

SELECT Product, Price
FROM Sales
ORDER BY Price DESC;

DESC sorts values in descending order. ASC sorts them in ascending order.

7.5 Calculate Total Revenue

Question: Calculate total revenue using quantity and price.

SELECT SUM(Quantity * Price) AS TotalRevenue
FROM Sales;

The expression calculates revenue for each row and SUM adds the results. Business rules for nulls, discounts, taxes, and returns should be applied consistently.

7.6 Count Total Records

SELECT COUNT(*) AS TotalRecords
FROM Sales;

COUNT(*) returns the number of rows in the table, including rows containing null values.

7.7 Find Unique Cities

SELECT DISTINCT City
FROM Sales;

DISTINCT returns unique city values. It does not automatically correct spelling differences or inconsistent capitalization.

7.8 Group Revenue by City

SELECT
    City,
    SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY City
ORDER BY TotalRevenue DESC;

GROUP BY creates a group for each city, and SUM calculates the revenue for each group.

7.9 Filter Groups Using HAVING

Question: Find cities where total revenue exceeds 20,000.

SELECT
    City,
    SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY City
HAVING SUM(Quantity * Price) > 20000;

WHERE filters rows before grouping, while HAVING filters groups after aggregation.

7.10 Filter Products Within a Price Range

SELECT Product, Price
FROM Sales
WHERE Price BETWEEN 1000 AND 15000;

BETWEEN includes both endpoints in standard SQL comparisons.

8. SQL Joins and Table Combination

Relational databases often store customers, orders, and products in separate tables. SQL joins combine related records using matching keys.

8.1 Types of SQL Joins

8.2 INNER JOIN Example

Assume Customers contains CustomerID and CustomerName, while Orders contains OrderID, CustomerID, and Amount.

SELECT
    c.CustomerName,
    o.OrderID,
    o.Amount
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID;

This query returns orders that match a customer record.

8.3 LEFT JOIN Example

Question: Find all customers, including those who have not placed orders.

SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerID = o.CustomerID;

Customers without matching orders remain in the output, with null values in the order columns.

8.4 Common Join Mistake

A join can unexpectedly increase the number of rows if the join key is not unique on one or both sides. This often happens when a table contains multiple records for the same customer or product.

Before joining tables, check key uniqueness, identify the expected relationship, and compare the row counts and totals before and after the operation.

9. SQL Data Cleaning Questions

9.1 Find Null Values

SELECT *
FROM Customers
WHERE Email IS NULL;

Use IS NULL to identify null values. In SQL, comparing a value with = NULL does not correctly test whether it is null.

9.2 Replace Null Values in Query Results

SELECT
    CustomerID,
    COALESCE(City, 'Unknown') AS City
FROM Customers;

COALESCE returns the first non-null expression. This changes the query output, not the stored table.

9.3 Remove Leading and Trailing Spaces

SELECT
    TRIM(CustomerName) AS CleanCustomerName
FROM Customers;

TRIM removes leading and trailing spaces in supported SQL systems. Other inconsistencies may require additional cleaning logic.

9.4 Identify Duplicate Email Addresses

SELECT
    Email,
    COUNT(*) AS RecordCount
FROM Customers
WHERE Email IS NOT NULL
GROUP BY Email
HAVING COUNT(*) > 1;

This query identifies non-null email values appearing in multiple rows. Whether an email address should uniquely identify a customer depends on the business rules.

9.5 Filter Records by Date Range

SELECT *
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

This half-open date range retrieves records from 2026, including timestamp values throughout the year. Adapt date literal syntax if required by the database system.

10. Append Queries and Merge Queries

Power Query provides Append Queries and Merge Queries to combine data from multiple tables or sources.

10.1 Append Queries

Append combines rows from multiple tables into one table. It is similar to stacking tables vertically.

Example: January Sales and February Sales have similar column structures. Appending them creates one table containing records from both months.

1. Open Power Query Editor.
2. Select Append Queries.
3. Choose two tables or three or more tables.
4. Review the resulting column structure.
5. Validate the row count and data types.

10.2 Merge Queries

Merge combines related tables using matching columns and adds information from the related table.

Example: Merge Sales with Products using ProductID to add product names and categories.

1. Select the primary query.
2. Choose Merge Queries.
3. Select the second query.
4. Select the matching key columns.
5. Choose the appropriate join type.
6. Expand the required fields from the merged table.
7. Check for unmatched records and unexpected row multiplication.

10.3 Append vs. Merge

Interview question: When should you use Append instead of Merge?

Answer: Use Append when adding records from tables with compatible structures. Use Merge when retrieving related information from another table based on matching keys.

11. Power Query vs. SQL

Power Query is particularly useful for visual, repeatable data preparation. SQL is useful for retrieving and processing data within relational databases. In many projects, both tools are used together.

12. Query Folding and Performance Optimization

Query folding occurs when Power Query translates supported transformation steps into operations that can be executed by the underlying data source. For relational databases, these operations may be translated into SQL.

12.1 Benefits of Query Folding

• Reduces the amount of data transferred from the source.
• Can improve refresh performance.
• Allows capable database systems to perform filtering and aggregation.
• Can reduce processing requirements in Power BI.

12.2 Performance Optimization Best Practices

• Remove unnecessary columns and rows.
• Use appropriate data types.
• Filter data early when the operation is valid and supported.
• Avoid transformations that are not required for reporting.
• Check whether query folding is available for the connector and steps used.
• Disable loading for intermediate queries that do not need to be loaded into the model.
• Use suitable database queries for large datasets when appropriate.
• Validate results after optimization to ensure the logic remains correct.

12.3 Interview Question: What Is Query Folding?

Answer: Query folding translates supported Power Query transformations into operations performed by the data source. It can improve performance by processing data closer to where it is stored. However, not all data sources and transformation steps support folding.

13. Scenario-Based Power Query Questions

Scenario 1: Combining Monthly Sales Files

Problem: You receive 12 CSV files and need to create a yearly sales dataset.

Solution:

1. Connect to the folder containing the files.
2. Combine files with compatible structures.
3. Standardize column names and data types.
4. Handle missing values and errors.
5. Validate that all expected months are included.
6. Compare the row count and revenue total against the source files.

Scenario 2: Inconsistent Product Names

Problem: The same product appears as "Laptop", "laptop", and "Laptop ".

Solution: Trim unnecessary spaces, normalize case where appropriate, and use a mapping table to resolve known spelling variations. Confirm that the values represent the same product before standardizing them.

Scenario 3: Invalid Date Values

Problem: Some dates cannot be converted into the Date data type.

Solution: Inspect the original values, verify the date format and locale, correct invalid source data where possible, and explicitly handle conversion errors. Do not automatically discard problematic records without investigating their significance.

Scenario 4: Duplicate Customer Records

Problem: Multiple rows represent what appears to be the same customer.

Solution: Identify the correct customer key, inspect differences between records, and define a rule for selecting or consolidating records. Similar names do not necessarily identify the same person or organization.

Scenario 5: Incorrect Revenue Totals

Problem: Power BI displays a higher revenue total than the source system.

Solution:

• Check for duplicate records.
• Inspect joins for unexpected row multiplication.
• Verify the level of detail in each table.
• Review discounts, taxes, cancellations, and returns.
• Confirm data types and calculation logic.
• Compare totals using the same date range and business rules.

14. Scenario-Based SQL Questions

14.1 Find the Highest-Priced Product

SELECT Product, Price
FROM Sales
WHERE Price = (
    SELECT MAX(Price)
    FROM Sales
);

This query returns all rows tied for the highest price.

14.2 Find the Second-Highest Distinct Price

SELECT MAX(Price) AS SecondHighestPrice
FROM Sales
WHERE Price < (
    SELECT MAX(Price)
    FROM Sales
);

This returns the second-highest distinct price if one exists. It returns null if there is no lower distinct price.

14.3 Find Customers Without Orders

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

This identifies customers without matching order records, assuming OrderID is non-null for genuine orders.

14.4 Calculate Revenue by Product

SELECT
    Product,
    SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Product
ORDER BY TotalRevenue DESC;

This query calculates revenue for each product and sorts the results from highest to lowest.

14.5 Find Orders Above Average Order Value

SELECT OrderID, Amount
FROM Orders
WHERE Amount > (
    SELECT AVG(Amount)
    FROM Orders
);

This returns orders with an Amount greater than the average of the non-null Amount values.

15. Important SQL Functions for Data Analysts

16. Common Data Transformation Errors and Solutions

17. Practical Exercises

Exercise 1: Clean a Sales Dataset

Import a sales CSV file into Power Query and complete the following tasks:

• Promote the first row to column headers if necessary.
• Assign appropriate data types.
• Remove unnecessary columns.
• Identify and handle null values.
• Investigate duplicate records using the correct business key.
• Standardize product and city names.
• Create a Revenue column using Quantity multiplied by Price.
• Validate the final row count and revenue total.

Exercise 2: SQL Query Practice

Using a Sales table, write queries to:

1. Retrieve all sales from Mumbai.
2. Find products priced above 5,000.
3. Calculate total revenue.
4. Count all sales records.
5. Find unique cities.
6. Calculate revenue by city.
7. Find cities with revenue above 20,000.
8. Sort products by price in descending order.
9. Identify records where Quantity is null.
10. Find duplicate OrderID values.

Exercise 3: Combine Related Tables

Use Sales and Products tables. Merge them using ProductID, expand ProductName and Category, and compare the resulting row count and revenue totals against the original Sales table.

Exercise 4: Monthly Sales Reporting Project

Prepare a monthly sales dataset from several files.

• Combine the source files.
• Standardize column names and data types.
• Handle missing or invalid values.
• Calculate revenue.
• Summarize revenue by month, product, and city.
• Validate the results against independent totals.
• Document transformation steps and assumptions.

18. Interview Questions and Answers

Q1. What is Power Query?

Answer: Power Query is a data connectivity and transformation tool that imports data from supported sources and applies repeatable preparation steps before loading it into a destination.

Q2. What is the difference between Power Query and DAX?

Answer: Power Query prepares and transforms data before it is loaded into the model. DAX is primarily used to create measures, calculated columns, and calculated tables in the data model.

Q3. What is the difference between Merge and Append?

Answer: Merge combines related tables using matching columns, while Append combines records by stacking rows from multiple tables.

Q4. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping, while HAVING filters groups after aggregation.

Q5. What is the difference between UNION and UNION ALL?

Answer: UNION removes duplicate result rows, while UNION ALL retains duplicates. UNION ALL may be more efficient when duplicate elimination is unnecessary.

Q6. What is the difference between INNER JOIN and LEFT JOIN?

Answer: INNER JOIN returns matching rows from both tables. LEFT JOIN retains all rows from the left table and returns matching right-side information when available.

Q7. Why are data types important?

Answer: Correct data types enable appropriate calculations, sorting, filtering, date operations, and relationships while reducing conversion errors.

Q8. How can duplicates affect a Power BI report?

Answer: Duplicates can inflate totals, distort counts, and cause incorrect aggregations. Their effect depends on the intended level of detail and the relationships between tables.

Q9. What is query folding?

Answer: Query folding translates supported Power Query transformations into operations performed by the underlying data source.

Q10. How do you validate transformed data?

Answer: Compare row counts, distinct key counts, null counts, aggregate totals, date ranges, and representative records before and after transformation.

Q11. What is the purpose of GROUP BY?

Answer: GROUP BY organizes records into groups based on specified columns, allowing aggregate functions to calculate results for each group.

Q12. How should missing values be handled?

Answer: Investigate why the values are missing, then retain, correct, replace, or remove them according to documented business rules.

Q13. What is the purpose of DISTINCT?

Answer: DISTINCT returns unique combinations of selected columns. It does not automatically correct spelling variations or identify the most accurate record.

Q14. What is a primary key?

Answer: A primary key uniquely identifies each row in a table and cannot contain null values. It helps establish reliable relationships between tables.

Q15. Why should transformation steps be documented?

Answer: Documentation improves maintainability, auditability, troubleshooting, reproducibility, and communication between analysts.

19. Best Practices for Power Query and SQL

• Understand the business requirements before transforming data.
• Keep source data accessible so transformations can be reproduced.
• Use clear and descriptive query, column, and step names.
• Assign data types explicitly and verify automatic type detection.
• Define business keys before removing duplicates.
• Check joins for unexpected row multiplication.
• Use source-side filtering and aggregation where appropriate.
• Preserve raw values when auditability is important.
• Handle nulls and errors according to their business meaning.
• Validate row counts and aggregate totals after major transformations.
• Assess query folding and refresh performance for large datasets.
• Test transformations on representative records.
• Document assumptions, exceptions, and data quality issues.

20. Quick Revision Checklist

• Understand Power Query Editor and Applied Steps.
• Practice filtering rows and selecting or removing columns.
• Learn data type conversion and null handling.
• Understand duplicate detection and business keys.
• Practice splitting columns, replacing values, and creating custom columns.
• Understand the basic structure of M language.
• Write SQL queries using SELECT, WHERE, ORDER BY, and DISTINCT.
• Practice SUM, COUNT, AVG, MIN, MAX, GROUP BY, and HAVING.
• Understand INNER JOIN, LEFT JOIN, UNION, and UNION ALL.
• Differentiate Append Queries from Merge Queries.
• Understand query folding and refresh performance.
• Validate transformations with row counts, keys, and aggregate totals.

21. Conclusion

Practicing Power Query, SQL, and data transformation questions develops the core skills needed for Power BI development and data analysis. Power Query provides a repeatable environment for data preparation, while SQL helps analysts retrieve and manipulate information efficiently in relational databases.

By practicing data cleaning, filtering, joins, aggregation, null handling, duplicate detection, and validation, learners can build reliable data preparation workflows and troubleshoot common reporting problems. These skills are useful for real-world analytics projects, technical interviews, and data analyst roles.

Explore the Microsoft Power BI Training course to learn more about the training, or visit the course demo registration page for additional information.