Data Cleaning, Transform & Load Data

1. What is the main purpose of Power Query in Power BI?

• To design dashboards

• To clean, shape, and transform data before analysis

• To calculate profit margins

• To export visuals to Excel

Answer explanation

The main purpose of Power Query in Power BI is to clean, shape, and transform data before analysis. This process ensures that the data is accurate and ready for insightful reporting and visualization.

In Power Query, which area lists all the actions you’ve performed on a dataset?

• Query Pane

• Applied Steps Pane

• Formula Bar

• Column Profile

Answer explanation

The Applied Steps Pane in Power Query displays all the actions you've performed on a dataset, allowing you to track and manage your transformations easily. This is the correct area for viewing your applied steps.

Which of the following correctly describes ETL in Power BI?

• Extract, Transfer, Load

• Extract, Transform, Load

• Evaluate, Transform, Load

• Edit, Transform, Link

Answer explanation

ETL stands for Extract, Transform, Load, which is a process used in Power BI to gather data from various sources, transform it into a suitable format, and then load it into a data model for analysis.

What is the main difference between ETL and ELT?

• ETL loads data first, ELT transforms it later

• ETL transforms data before loading, ELT transforms after loading

• Both mean the same thing

• ETL is slower than ELT

Answer explanation

The main difference is that ETL (Extract, Transform, Load) transforms data before loading it into the target system, while ELT (Extract, Load, Transform) loads the data first and then transforms it afterward.

Which Power Query operation combines tables vertically (one below another)?

• Merge Queries

• Append Queries

• Join Tables

• Group Queries

Answer explanation

The correct choice is 'Append Queries' because this operation combines tables vertically, stacking them one below another, unlike 'Merge Queries' or 'Join Tables', which combine them horizontally.

What language does Power Query use for writing transformation logic?

• DAX

• Python

• M Language

• SQL

Answer explanation

Power Query uses M Language (Data Mashup language) to define transformation steps.

Why is it important to check data types before loading to Power BI?

• To make dashboards colorful

• Incorrect types may cause wrong results or errors in visuals

• To reduce the file size

• It has no real impact

Answer explanation

Checking data types is crucial because incorrect types can lead to wrong results or errors in visuals, affecting the accuracy and reliability of your Power BI dashboards.

When you use “Split Columns” in Power Query, what happens?

• The table splits into two smaller tables

• A single column is divided into multiple columns

• Two columns merge into one

• The column is deleted

Answer explanation

Using 'Split Columns' in Power Query allows you to divide a single column into multiple columns based on a specified delimiter or criteria, making it easier to analyze and manipulate data.

What tool or section allows you to create new calculated columns in Power Query?

• Home tab

• Add Column tab

• Transform tab

• View tab

Answer explanation

The Add Column tab in Power Query is specifically designed for creating new calculated columns. It provides various options to add custom calculations to your data, making it the correct choice for this task.

In the ETL concept shown for Power BI, what happens during the "Transform" step?

• The data is visualized in Power BI

• The data is analyzed using DAX formulas

• The data is cleaned and structured using Power Query

• The data is exported to Excel

Answer explanation

During the 'Transform' step in ETL for Power BI, the data is cleaned and structured using Power Query. This process prepares the data for analysis and visualization, ensuring it is in the right format.