extract transform and load data in power bi coursera week 2 answers

Self-review: Preparing a dataset

1. Several columns in your worksheet contain missing or null values. Which of the following options must you type in the Value to Find field to locate these values?

  • 0
  • Missing
  • Null

2. What steps did you take to address inconsistencies in date columns in your worksheet? Select all that apply.

  • You changed the data type of the column to Text by clicking on the DataType icon next to the column name.
  • You dropped duplicate rows by selecting Remove Duplicates inside the Remove Rows menu.
  • You changed the data type of the column to Date by clicking on the Data Type icon next to the column name.
  • You replaced any empty values with a default date using the Replace Values tool.

3. True or false: Once you completed all the data cleaning steps, you clicked Apply or Close & Apply to apply all the transformations you made.

  • True

  • False

Knowledge Check: Introduction to transforming data in Power BI

4. Which of the following operations are steps in the data transformation process? Select all that apply.

  • Creating insights from data
  • Shaping data

  • Cleaning data
  • Removing data

5. Which of the following data types are part of the number type group? Select all that apply.

  • Whole number
  • Fixed decimal number
  • Text
  • Binary

6. Which one of the following features are used to track, re-order or delete the steps completed in Power Query?

  • Applied Steps
  • New Source
  • Properties Queries

7. Which of the following options can be used for Power Query Optimization? Select all that apply.

  • Filter rows in the queries.

  • Choose the right data types for columns.

  • Choose only the columns that you will use in the data model.

8. 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.

  • Skewed statistical results

  • Incorrect calculations
  • Normal distribution in data
  • Misleading insights

Knowledge check: Basic data sources

9. Which table do you use as the first table in the AppendQueries window?

  • AdventureWorksSales
  • Consolidated Sales
  • OtherSales

10. How many columns are there in the new query ConsolidatedSales, after you combined the AdventureWorksSales and OtherSales queries?

  • 24
  • 8
  • 16

11. True or False: To append all respective columns between two queries successfully, you first need to make sure that there is an equal number of columns, the same column names and the same or convertible data types respectively.

  • False
  • True

12. How many rows are there in the new query Consolidated Sales, after you combined AdventureWorksSales and OtherSales queries?

  • 24
  • 124
  • 100

Knowledge Check: Advanced data transformations in Power BI

13. Which feature allows you to add rows of different data sources together in Power Query?

  • Grouping
  • Merging
  • Appending

  • Removing Data

14. Which of the following can be considered as a reason for combining data? Select all that apply.

  • Creating relationships

  • Visualizing data
  • Consolidating information

  • Simplifying data management

15. True or False: You can combine the tables in two different ways: merging and appending.

  • True

  • False

16. You import two Microsoft Excel tables named ContactInfo and Address into Power Query.

Address contains the following columns:

CustomerID

CustomerName

Phone

Address

ContactInfo contains 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.

  • Remove ZipCode from ContactInfo
  • Change the column naming in ContactInfo from ContactID to CustomerID and ContactName to CustomerName

  • Add a calculated column to the Address table
  • Check the column types and make sure they have the same types respectively.

17. 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?

  • 5 Columns, 18 Rows
  • 9 Columns, 30 Rows
  • 5 Columns, 30 Rows
  • 7 Columns, 30 Rows

Self-review: Merging two data sources

18. How many columns are there in the final Consolidated Sales dataset, after you merged the (Sales) and (Product) queries, add (Product.Product) and remove (Reseller), (Employee) and (Sales Territory Key) columns?

  • 10
  • 8
  • 7

19. True or false: After you import two data sources to combine, you observed that there is no NULL value ProductKey column for the Sales table. You applied Left outer join to merge the tables. If you had used Inner join, there would not have been a change in the number of rows because there are no null values in the product column.

  • True
  • False

20. Exactly how many rows are there in the Sales query, after you merged the Sales and Product queries?

  • 999
  • 57851
  • 47655

Knowledge check: Transformations by combining data

21. Which feature allows you to combine related data between differently structured data sources in Power Query?

  • Merging
  • Grouping
  • Appending

22. Which of the following can be considered as a purpose of merging data with joins? Select all that apply:

  • Integrating Data

  • Matching Related Data

  • Expanding Data
  • Exploring Relationships

23. 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.

  • True
  • False

24. 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:

  • 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 EmployeeKey column.
  • Merge the Sales table with the Product, Reseller and Employee tables respectively.

25. 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 CategoryKey column. What should you do to accomplish this task?

  • Merge Product and Categories tables based on CategoryKey column by choosing Inner Join in the join kind dropdown.
  • Merge Product and Categories tables based on CategoryKey column by choosing Left Outer Join in the join kind dropdown.
  • Merge Product and Categories tables based on ResellerKey column.

Module quiz: Transforming data in Power BI

26. A join is a method to combine ________ from two or more tables based on ________.

  • “columns” and “a common value”
  • “rows” and “a related column”
  • “datasets” and “a specific attribute”

27. True or False: You can use Append Queries to combine customer data from Adventure Works database and potential customers data from a separate Excel file.

  • True
  • False

28. What is a join key in the context of combining tables with merge?

  • A column or set of columns used to establish a relationship between tables during a join operation.
  • An index created on a table to optimize query performance.
  • A unique identifier assigned to each record in a table.

29. True or False: In Power BI, the left join type includes all rows from both tables, including unmatched rows.

  • True
  • False

30. The merge operation is used to ________ tables based on ________.

  • filter, specific values
  • sort, ascending order
  • combine, related columns

31. Which of the following options can be considered as the purpose of a join operation?

  • Creating insights from data
  • Adding new relationships
  • Ensuring consistency

32. When merging two tables which of the following conditions are required to match specific columns? Select all that apply:

  • Matching columns should have a numeric data type.
  • Matching columns should have convertible data types.
  • Matching columns should have the same data type.

33. What is the purpose of transforming data in Power BI?

  • To create visualizations and reports.
  • To clean, filter, and manipulate data for analysis.
  • To share dashboards and reports.

34. Data transformation involves ________ and ________ data to fulfill analysis requirements.

  • cleaning and visualizing
  • modifying and enhancing
  • collecting and storing

35. A dataset contains two tables with related SalesID columns. The second table includes date information for the sales. What should you do to visualize the column containing date data in the second table?

  • To join two tables
  • To format data
  • To merge columns

36. True or False: A join is a method for combining columns from two or more tables based on a related column.

  • True
  • False

37. True or False: A join key is a column that exists in only one table being joined.

  • True
  • False

38. Which join type returns all the records from the left table and the matching records from the right table?

  • Full outer join
  • Left outer join
  • Right outer join

39. True or False: The merge operation in Power BI allows you to combine tables based on related columns.

  • True
  • False

40. Before appending, a join can be performed to check for any ________ in the common columns or keys.

  • Dependencies
  • Correlations
  • Inconsistencies

41. True or False: Joining two tables requires that the primary key from the first table matches a corresponding key in the second table.

  • True
  • False

42. In Power BI, data transformation involves ____________ to make it convenient for data analysis.

  • creating visualizations and reports
  • cleaning, filtering, and manipulating data

  • importing data from external sources

43. What is the purpose of cleaning and formatting data in Power BI?

  • Removing inconsistencies and errors in the data.
  • Creating visualizations and reports.
  • Creating reports and dashboards.

44. How can you combine the outcomes of two queries in Power BI, ensuring that the results of one query are stacked directly beneath the other?

  • Merge the query results.
  • Append the query results.
  • Join the query results.

45. What is a join in the context of Power BI and data analysis?

  • A method to combine rows from two or more tables based on a related column.
  • A method to filter data based on specific criteria.
  • A method to organize data in columns and rows.

46. A join key is typically a ____ ___ that exists in both tables being joined.

  • “calculated value”
  • “primary key”
  • “common column”

47. What is the purpose of combining tables with the merge operation in data analysis?

  • To combine data from multiple tables into a table based on matching values.
  • To remove duplicate rows from a single table.
  • To create a new table with new columns.

48. True or False: In Power BI, data transformation involves modifying the structure and format of data to meet analysis requirements.

  • True
  • False

Leave a Reply