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

Knowledge Check: Loading data with Power BI

1. Which step in the ETL process includes sending the transformed data into the target database?

  • Extract
  • Load

  • Transform

2. Which of the following options can describe staging? Select all that apply.

  • Used primarily in Exploring Relationships.
  • 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.

  • Acts a bridge between the data sources and the data warehouse.

3. True or False: The existence of a data staging area is not obligatory for your ETL jobs.

  • True

  • False

4. You import two Microsoft Excel tables named SalesData2020 andSalesData2021 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.

  • Load data of SalesData2020 and SalesData2021 tables.
  • Import SalesData2020 and SalesData2021 tables.

  • Right-click the tables and disable the load by clearing the checkbox Enable load.

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

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

  • Close & Apply

  • Apply
  • Apply

Self-review: Profiling a dataset

In the exercise Profiling a dataset, you imported an Excel file and used profiling tools to identify data anomalies in Power Query.

To do so, you had to complete the following tasks:

Detect empty values in ProductKey column.

Assess the distribution of Product Categories.

Detect potential anomalies in the Price Column.

Your final worksheet should look like this:

You can use the following questions to make sure that you understood and executed the tasks correctly. Don’t forget that you can revisit the previous learning items to recap the process steps.

6. Which of the following operation gives you the percentage of valid, error, or empty values found in columns?

  • Column quality

  • Column distribution
  • Column profile

7. What is the empty percentage of ProductKey column rows?

  • 13
  • 11

  • 16

8. How many unique and distinct values are there in the Price column?

  • 18 distinct, 17 unique

  • 18 distinct, 18 unique
  • 17 distinct, 17 unique

Knowledge Check: Data profiling in Power BI

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

  • Column Quality
  • Column Distribution
  • Column Profile

10. Which of the following is defined as a data point that significantly deviates from other observations?

  • Anomaly
  • Outlier

  • Standard Deviation

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

  • True

  • False

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

  • 3 unique and 7 distinct
  • 2 unique and 8 distinct
  • 2 unique and 5 distinct

  • 1 unique and 5 distinct

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

  • Column Profile

  • Column Distribution
  • Column Quality

Knowledge check: Advanced queries

14. Which of the following statements is true about Power BI dataflows?

  • Dataflows enable self-service data preparation and integration.

  • Dataflows are only available in Power BI Desktop.
  • Dataflows allow advanced coding for complex transformations.
  • Dataflows can only connect to cloud-based data sources.

15. Which of the following join types is selected when merging or joining multiple queries as the most efficient merge strategy?

  • Inner join

  • Left outer join
  • Full outer join

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

  • True

  • False

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

  • It provides advanced scripting capabilities for customizing data transformations.
  • It offers a wide range of visualizations to present the analysis results.
  • It allows you to import and connect to various data sources to consolidate the dataset.

18. Which of the following terms can be considered as the benefits of Reference Queries? Select all that apply:

  • Scalability
  • Efficiency

  • Connectivity
  • Reusability

Module Quiz: Advanced ETL in PowerBI

19. The ________ provides syntax highlighting, auto-completion, and error-checking features, making it easier to write and debug your ________.

  • Transform Data, DAX
  • Advanced Editor, M code
  • Advanced Editor, DAX

20. True or False: When loading data into Power BI for offline analysis, you should choose the Direct Query option.

  • True
  • False

21. Which of the following is a definition of Data profiling?

  • Aggregating columns containing numeric data.
  • Creating a Data model.
  • Studying the nuances of the data.

22. If you need fine-grained control over data transformations or want to build a modular data model, ________ is the preferred option. On the other hand, if you want a scalable and user-friendly approach to data preparation, ________ offer a more intuitive experience.

  • dataflows, referencing queries
  • referencing queries, dataflows
  • referencing queries, merge queries

23. Choose appropriate ________ for columns to improve performance and data accuracy.

  • sorts
  • data types
  • filters

24. ________ provide a centralized platform for businesses to manage and organize their data effectively

  • Reference Queries
  • Dataflows
  • Advanced Editor and M Language

25. Using ________ you can perform advanced data manipulation tasks such as conditional filtering, custom column creation, data type conversions, and merging multiple data sources.

  • Dataflows
  • Reference Queries
  • M Language

26. True or False: By performing expensive operations such as sorting, grouping, or aggregating at the beginning of the data transformation pipeline, Power BI can optimize query execution and minimize the amount of data that needs to be processed during analysis, resulting in faster query response times.

  • True
  • False

27. Why is it important to consider authentication in Power BI connectors?

  • Authentication prevents unauthorized access to sensitive information.
  • Authentication improves the performance of data processing.
  • Authentication ensures the accuracy and integrity of data.

28. Which of the following statements regarding error handling in Power BI is true?

  • Error handling in Power BI lets you display error messages and does not provide options for resolution.
  • Error handling in Power BI is not necessary as errors are automatically handled by the system.
  • Error handling in Power BI allows you to define custom actions when encountering errors during data transformation or query execution.

29. Which query language do you use in Advanced Editor in Power Query?

  • T-SQL
  • DAX
  • M

30. You want to create a business report using Power BI that includes newly added data rows to a table. It is important for the data to be up to date, but you also need your report to run smoothly and efficiently. Which option would be the most appropriate to choose?

  • Direct Query
  • Dual Mode
  • Import

31. Which of the following statements about Power BI reference queries is true?

  • Reference queries provide a way to reuse query logic and transformations.
  • Reference queries enable efficient data loading.
  • Reference queries support data integration from multiple sources.

32. How many rows does Power Query scan to detect the type of data in the columns?

  • 100
  • 1000
  • 10000

33. True or False: Power BI dataflows can be used to fetch data from real-time data sources.

  • True
  • False

34. True or False: When selecting a connector in Power BI, the choice should solely be based on the availability of the connector, regardless of the data source and specific requirements of the project.

  • True
  • False

35. True or False: You may sometimes need to use M language to apply custom transformations which could not be created using only the Power Query user interface.

  • True
  • False

36. Which storage mode leaves the data at the data source?

  • Direct Query
  • Import
  • Dual

37. ____ helps you to gain a comprehensive understanding of data quality, structure, and distribution.

  • Extracting data
  • Transforming data
  • Profiling data

38. True or False: You can avoid repeating data transformation steps and ensure data consistency by using reference queries in Power BI.

  • True
  • False

39. Which of the following group names in M Language code shows the value of the calculation?

  • Let
  • Columns
  • Source

40. Which of the following best practices can help improve performance in Power BI?

  • Following the “Do expensive operations last” principle.
  • Following the “Do expensive operations first” principle.
  • Using M language in Advanced Editor for advanced transformations.

Leave a Reply