Welcome to the ultimate guide for Extract, Transform and Load Data in Power BI quiz answers! Whether you’re completing practice quizzes to refine your skills or preparing for graded quizzes, this post provides the solutions you need. Covering all modules of the course, this guide will help you master the ETL process using Power BI to efficiently extract, transform, and load data for analysis.
Extract, Transform and Load Data in Power BI Quiz Answers – Practice & Graded Quizzes for All Modules
Table of Contents
Extract, Transform and Load Data in Power BI Module 01 Quiz Answers
Knowledge check: Basic data sources Quiz Answers
Q1. What is the difference between a dataset and a data source in Power BI?
Correct Answer A dataset is a container that holds some of the data from a data source, whereas a data source is where the data actually comes from.
Explanation In Power BI, a data source is the origin of the data (e.g., an Excel file, database, etc.), while a dataset is the data structure or table created by Power BI that contains the data pulled from the source.
Q2. True or False: You can create datasets that are reusable across multiple reports using basic data sources, which saves you the time and effort of having to import the same data repeatedly for each report.
Correct Answer True
Explanation Creating reusable datasets allows users to import data once and use it across multiple reports, saving time and reducing redundancy.
Q3. What are the types of workbooks that Power BI supports? Select all that apply:
Correct Answers
- Workbooks with data models
- Workbooks with connections to external data sources
- Workbooks with ranges or tables of data
Explanation Power BI supports workbooks that include data models, connections to external sources, and ranges or tables of data. Workbooks with shapes and images are not supported for data analysis in Power BI.
Q4. What is the maximum size of an Excel workbook that can be uploaded to Power BI?
Correct Answer 1 GB
Explanation The maximum file size for an Excel workbook that can be uploaded to Power BI is 1 GB.
Q5. True or False: When using DirectQuery in Power BI, there is a limit on the number of rows that can be returned to your underlying data source.
Correct Answer True
Explanation When using DirectQuery, there are limitations on the number of rows that can be returned to Power BI from the data source, depending on the data source and configuration settings.
Knowledge check: Advanced data sources Quiz Answers
Q1. What makes Azure Blob Storage an ideal option for storing unstructured data, such as photos and videos, for Adventure Works’ online retail website?
Correct Answer Azure Blob Storage is a scalable and cost-effective Cloud Storage Service, suitable for storing large amounts of unstructured data.
Explanation Azure Blob Storage is designed to handle unstructured data, such as images and videos, and is highly scalable and cost-efficient for large data storage. It does not rely on SQL support or data structure transformation.
Q2. What is the purpose of the Data Source Settings option in Power BI?
Correct Answer To change the connection details and credentials for the data source.
Explanation The Data Source Settings in Power BI are used to manage the connection details and credentials associated with data sources, such as changing the location of files or modifying credentials.
Q3. Considering the data classification at Adventure Works for their online retail website, which type of data is most appropriately stored and queried using a SQL database?
Correct Answer Financial business data that is structured for year-to-year comparison and is mainly read-only.
Explanation Structured data, like financial business data, fits well within a SQL database where data is highly organized and can be queried for comparisons over time. Unstructured data like images or audio files would be better stored in a blob storage system.
Q4. True or False: In Power BI, triggers and actions are used primarily for visualizing data, enabling data analysts to create more complex and interactive reports.
Correct Answer False
Explanation Triggers and actions in Power BI are not primarily used for data visualization. They are used for automation purposes, such as refreshing datasets or sending notifications, not for creating interactive reports.
Q5. What is the primary purpose of setting up a scheduled data refresh in Power BI for a data analyst team?
Correct Answer To ensure that sales report datasets are regularly updated without manual intervention, improving data accuracy and workflow efficiency.
Explanation Scheduled data refreshes in Power BI automatically update the datasets at specified intervals, ensuring that the reports reflect the latest data without requiring manual updates. This improves workflow efficiency and data accuracy.
Extract, Transform and Load Data in Power BI Module 02 Quiz Answers
Knowledge Check: Introduction to transforming data in Power BI Quiz Answers
Q1. Which of the following operations are steps in the data transformation process? Select all that apply.
Correct Answer
- Shaping data
- Cleaning data
Explanation Data transformation involves operations such as shaping (structuring) and cleaning (removing errors or inconsistencies) the data to make it more suitable for analysis. Creating insights from data is a separate step that follows data transformation.
Q2. Which of the following data types are part of the number type group? Select all that apply.
Correct Answer
- Fixed decimal number
- Whole number
Explanation Fixed decimal numbers and whole numbers are part of the numeric data type group. Text and binary are not numeric types; they belong to other data groups.
Q3. Which one of the following features are used to track, re-order or delete the steps completed in Power Query?
Correct Answer Applied Steps
Explanation In Power Query, the “Applied Steps” pane is used to track, re-order, or delete the steps taken during the transformation process. This allows you to modify or undo previous steps.
Q4. Which of the following options can be used for Power Query Optimization? Select all that apply.
Correct Answer
- Choose the right data types for columns
- Filter rows in the queries
- Choose only the columns that you will use in the data model
Explanation Optimizing Power Query involves selecting the appropriate data types for columns, filtering unnecessary rows, and choosing only the relevant columns for the data model. These steps help reduce processing time and improve query performance.
Q5. You have added a new source that displays NaN (Not a Number) values in Power Query. Which of the following issues could occur if these are not resolved? Select all that apply.
Correct Answer
- Skewed statistical results
- Incorrect calculations
- Misleading insights
Explanation NaN values in the data can lead to incorrect calculations, skew statistical results, and ultimately provide misleading insights if they are not handled properly. They do not contribute to a normal distribution of data.
Knowledge Check: Advanced data transformations in Power BI Quiz Answers
Q1. Which feature allows you to add rows of different data sources together in Power Query?
Correct Answer
- Appending
Explanation Appending is the process of adding rows from different data sources together. Merging combines columns, not rows, from two data sources.
Q2. Which of the following can be considered as a reason for combining data? Select all that apply.
Correct Answer
- Simplifying data management
- Consolidating information
- Visualizing data
- Creating relationships
Explanation Combining data can help in various aspects like simplifying management, consolidating information, creating meaningful visualizations, and establishing relationships between different data sources for analysis.
Q3. True or False: You can combine the tables in two different ways: merging and appending.
Correct Answer
- True
Explanation You can combine tables in Power Query either by merging (combining columns) or appending (adding rows) depending on your needs.
Q4. You import two Microsoft Excel tables named ContactInfo and Address into Power Query.
Address contains the following columns:
- CustomerID
- CustomerName
- Phone
- Address
ContactInfo contains the following columns:
- ContactID
- ContactName
- ZipCode
- Phone
- Address
What should you do to append these two tables with ContactInfo as the primary table? Select all that apply.
Correct Answer
- Change the column naming in ContactInfo from ContactID to CustomerID and ContactName to CustomerName
- Check the column types and make sure they have the same types respectively.
Explanation To successfully append the tables, you must ensure that column names and types match across the tables. Renaming columns and ensuring consistent data types are important steps before appending.
Q5. You import 2 Microsoft Excel tables named ContactInfo and Address into Power Query. ContactInfo has 5 columns and 18 rows, whereas Address has 4 columns and 12 rows. Only 2 of the column names are common. You append the tables without changing the table columns. How many columns and rows are you going to have at the end of the append?
Correct Answer
- 9 Columns, 30 Rows
Explanation When appending tables with non-matching columns, Power Query will add a new column for each column that does not match, resulting in 9 columns (5 from ContactInfo and 4 from Address). The total number of rows will be the sum of both tables, which is 30 (18 rows from ContactInfo and 12 from Address).
Knowledge check: Transformations by combining data Quiz Answers
Q1. Which feature allows you to combine related data between differently structured data sources in Power Query?
Correct Answer
- Merging
Explanation Merging is the process used to combine related data between tables with different structures, typically based on common columns.
Q2. Which of the following can be considered as a purpose of merging data with joins? Select all that apply:
Correct Answer
- Expanding Data
- Integrating Data
- Matching Related Data
- Exploring Relationships
Explanation Merging data through joins allows you to expand data by combining related columns, integrate data from multiple sources, match related data using common keys, and explore relationships between different data sets.
Q3. True or False. The full outer join is useful when you want to retrieve all the records from both tables, regardless of whether they have matching values in the join condition.
Correct Answer
- True
Explanation A full outer join retrieves all records from both tables, even if they don’t have matching values in the join condition. This join ensures no data is lost from either table.
Q4. You import 4 Microsoft Excel tables named Sales, Product, Reseller and Employee into Power Query.
Sales contains the following columns:
- SalesOrderNumber
- OrderDate
- ProductKey
- ResellerKey
- EmployeeKey
- SalesTerritoryKey
- Quantity
- Unit Price
- Sales
- Cost
Your manager asked you to list Sales data with the descriptive information from the Product, Reseller and Employee tables for the columns which have the suffix “Key”. What should you do to accomplish this task? Select all that apply:
Correct Answer
- Check the column types of (ProductKey), (ResellerKey) and (EmployeeKey) in the Sales, Product, Reseller and Employee tables.
- Join Sales and Product tables based on the ProductKey column.
- Join Sales and Reseller tables based on the ResellerKey column.
- Merge the Sales table with the Product, Reseller, and Employee tables respectively.
Explanation To list the descriptive information from the related tables, you would need to first ensure the columns are of the same type. Then, you would perform merges based on the common “Key” columns between Sales and the other tables (Product, Reseller, and Employee).
Q5. You import two Microsoft Excel tables named Product and Categories into Power Query. There are 319 rows in the Product table. Nine of the total rows in the Product table do not have Categories data, so the CategoryKey of these rows has NULL values.
Your manager asked you to list Product data by showing their category names including the rows which have NULL values in the CategoryKey column. What should you do to accomplish this task?
Correct Answer
- Merge Product and Categories tables based on CategoryKey column by choosing Left Outer Join in the join kind dropdown.
Explanation A Left Outer Join will include all records from the Product table, even those with NULL values in the CategoryKey column, and match them with corresponding data from the Categories table where possible.
Extract, Transform and Load Data in Power BI Module 03 Quiz Answers
Knowledge Check: Loading data with Power BI Quiz Answers
Q1. Which step in the ETL process includes sending the transformed data into the target database?
Correct Answer
- Load
Explanation The Load step in the ETL process is responsible for loading the transformed data into the target database or data warehouse, making it available for analysis.
Q2. Which of the following options can describe staging? Select all that apply.
Correct Answer
- Acts as a bridge between the data sources and the data warehouse.
- Allows raw data to be stored and prepared for further processing in a data pipeline.
- Simplifies the process of data cleansing and consolidation for operational data.
Explanation Staging areas are used to temporarily store raw data, clean and consolidate it, and prepare it for loading into the data warehouse. This step facilitates the ETL process and acts as a bridge for data movement.
Q3. True or False: The existence of a data staging area is not obligatory for your ETL jobs.
Correct Answer
- True
Explanation A data staging area is not mandatory for every ETL process. While it is commonly used to simplify and manage data transformations, some processes may directly load data without the need for a staging area.
Q4. You import two Microsoft Excel tables named SalesData2020 and SalesData2021 into Power Query.
Both tables have the same column names:
- SalesDate
- ProductKey
- ResellerKey
- EmployeeKey
- Quantity
- Discount
- Unit Price
- Sales
Your manager asked you to transform these Excel tables, then append them but not use them in the data warehouse, just keep them as source tables. What should you do to accomplish this task? Select all that apply.
Correct Answer
- Right-click the Queries pane at the left menu of Power Query, select New Group and create a group. Then move the tables to the newly created group.
- Right-click the tables and disable the load by clearing the checkbox Enable load.
Explanation To prevent loading the tables into the data warehouse but still append them, you can group them in Power Query and disable the load, ensuring they remain only as source tables for processing.
Q5. When the Power Query window is opened and the transformations are completed, you are ready to load the tables into the reporting area. What should you do to complete this task?
Correct Answer
- Close & Apply
Explanation The “Close & Apply” option in Power Query is used to apply the transformations and load the data into the reporting area, making it ready for further analysis.
Knowledge Check: Data profiling in Power BI Quiz Answers
Q1. Which of the following Power Query menu items provides the user with column information like the number of empty and distinct rows and rows with errors?
Correct Answer
- Column Profile
Explanation The “Column Profile” feature in Power Query provides detailed information about columns, including the number of empty rows, distinct values, and rows with errors.
Q2. Which of the following is defined as a data point that significantly deviates from other observations?
Correct Answer
- Outlier
Explanation An “Outlier” refers to a data point that significantly deviates from other observations in a dataset. It is an anomaly but typically stands out due to its extreme values.
Q3. True or False: Distinct is known as “total number of different values”, regardless of how many of each we have. Unique is known as “total number of values that only appear once”. In this case, for fields with Primary Key or Unique Constraint defined, the values of Unique and Distinct will be equal.
Correct Answer
- True
Explanation Distinct refers to the total number of unique values, while Unique refers specifically to those that appear only once. In fields with a Primary Key or Unique Constraint, the values of Unique and Distinct will indeed be equal.
Q4. You import an Excel table named EmployeeData2023 into Power Query. You removed all other columns except Country.
The Country column has the following 10 row values:
- USA
- France
- France
- Ireland
- England
- England
- USA
- USA
- Spain
- France
What are the unique and distinct values of this column?
Correct Answer
- 3 unique and 7 distinct
Explanation The “unique” values are those that appear only once (Ireland, Spain), and the “distinct” values are all the different values in the column (USA, France, Ireland, England, Spain), totaling 7.
Q5. You need to identify if data in a column contains Empty values. Which of the following can be used to quickly identify this? Select all that apply.
Correct Answer
- Column Profile
- Column Quality
Explanation Both “Column Profile” and “Column Quality” in Power Query can be used to identify and analyze empty values in a column. “Column Profile” provides detailed metrics, while “Column Quality” gives a quick summary of data quality, including empty values.
Knowledge check: Advanced queries Quiz Answers
Q1. Which of the following statements is true about Power BI dataflows?
Correct Answer
- Dataflows enable self-service data preparation and integration.
Explanation Power BI dataflows allow users to perform self-service data preparation and integration, which helps in creating reusable data transformation processes across reports and dashboards.
Q2. Which of the following join types is selected when merging or joining multiple queries as the most efficient merge strategy?
Correct Answer
- Inner join
Explanation An “Inner join” is typically the most efficient merge strategy, as it only includes rows where there is a match in both datasets, reducing the data being processed.
Q3. True or False: You prioritize and schedule resource-intensive operations towards the end of the data transformation process. This approach ensures that complex calculations, merging large datasets, and applying multiple transformations on a significant number of rows are executed efficiently, leading to faster data loading and more responsive reports.
Correct Answer
- True
Explanation By prioritizing and scheduling resource-intensive operations towards the end, you ensure that the transformation process is efficient, reducing the load on earlier steps and ensuring that only necessary data is processed later in the workflow.
Q4. You are working on a data analysis project for Adventure Works. The company provides you with a large dataset containing information about sales, production, and inventory. Your task is to perform complex data transformations and calculations to derive meaningful insights from the data.
In this scenario, how can the Advanced Editor in Power BI be beneficial for your data analysis process?
Correct Answer
- It provides advanced scripting capabilities for customizing data transformations.
Explanation The Advanced Editor in Power BI allows for more advanced and customized data transformations through M code scripting, enabling complex calculations and transformations that are beyond the default options in Power Query.
Q5. Which of the following terms can be considered as the benefits of Reference Queries? Select all that apply:
Correct Answer
- Reusability
- Efficiency
Explanation Reference Queries provide the benefits of reusability (allowing one query to reference others) and efficiency (by reducing the need to duplicate data transformation steps). They are not directly associated with connectivity or scalability.
Frequently Asked Questions (FAQ)
Are the Extract, Transform and Load Data in Power BI quiz answers accurate?
Yes, these answers are thoroughly verified to match the latest course content and Power BI features.
Can I use these answers for both practice and graded quizzes?
Absolutely! These answers are designed to assist you with both practice and graded quizzes, ensuring thorough preparation.
Does this guide cover all modules in the course?
Yes, this guide includes answers for every module of the Extract, Transform and Load Data in Power BI course.
Will this guide help me better understand the ETL process in Power BI?
Yes, by following these answers, you will not only prepare for quizzes but also strengthen your understanding of how to efficiently handle data using Power BI’s ETL capabilities.
Conclusion
We hope this guide to Extract, Transform and Load Data in Power BI quiz answers helps you succeed in mastering the ETL process. Bookmark this page for quick reference and share it with your classmates. Ready to unlock the full potential of Power BI in your data transformation tasks? Let’s dive in and ace the course!
Resource: Extract, Transform and Load Data in Power BI
Get All Course Quiz Answers of Microsoft Power BI Data Analyst Professional Certificate >>
Preparing Data for Analysis with Microsoft Excel Quiz Answers
Harnessing the Power of Data with Power BI Quiz Answers
Extract, Transform and Load Data in Power BI Quiz Answers