Module 4: Graded Assessment and Course Wrap Up

Contents hide

1 Week 4: Leadership and Influencing Skills

2 INTRODUCTION – Graded Assessment and Course wrap up

3 SELF-REVIEW: TRANSFORMING MULTIPLE DATA SOURCES

4 COURSE QUIZ: EXTRACT, TRANSFORM AND LOAD DATA IN POWER BI

5 CONCLUSION – Graded Assessment and Course wrap up

Spread the love

Week 4: Leadership and Influencing Skills

INTRODUCTION – Graded Assessment and Course wrap up

This module provides a thorough assessment that allows you to demonstrate your proficiency in the key skills and knowledge areas covered throughout the course. By testing your ability to apply the concepts, techniques, and tools you’ve learned, it ensures that you’re well-prepared for more complex topics and real-world applications in your field.

Through practical exercises, projects, and examinations, you will have the opportunity to showcase your understanding and identify areas for improvement. This assessment serves as both a reinforcement of your learning and a critical checkpoint, giving you the clarity and confidence to progress.

Learning Objectives:

Synthesize the skills you’ve gained from this course by completing the graded assessment.
Reflect on the course content, considering both the progress you’ve made and the learning path ahead.

SELF-REVIEW: TRANSFORMING MULTIPLE DATA SOURCES

1. What is the number of columns remaining after you remove the unnecessary ones from the OrderDetails query?

3
4 (CORRECT)
5

That’s correct! You keep SalesOrderID, ProductID, OrderQty, UnitPrice columns. This means four columns remain.

2. True or False: In the Order Details query, when you remove the anomaly values there are 27distinct, and 6unique values for the UnitPrice column.

True
False (CORRECT)

That’s correct! There are 24 distinct and 3 unique values for the column Unit Price in the Order Details query after you remove the anomaly rows.

3. In the OrderDetails query, how many rows are there after you remove anomalies?

997 rows (CORRECT)
999 rows
996 rows

That’s correct! There are 997 rows for the OrderDetails query, which can be verified from the total amount of columns and rows from the information bar at the bottom left corner of Power Query after you remove the columns, detect the anomalies, filter, and remove the anomaly values from the specific column.

4. What is the reason for using an Inner Join for the merge operation of OrderDetails and Order?

To keep all the rows from the right table.
To keep only matching rows between the two table. (CORRECT)
To keep all the rows from the left table.

That’s correct. This will join the order details to orders that have a matching SalesOrderID column.

5. By removing data anomalies in the sales data, what impact does this have on the business?

Select all that apply.

It improves decision making. (CORRECT)
It improves integrity of the data source.
It improves data accuracy. (CORRECT)

Exactly! Data quality plays a pivotal role in the decision-making process. When the data is accurate, reliable, and well-organized, it provides a solid foundation for making informed business decisions. However, poor-quality data can lead to inaccurate insights, which in turn can result in suboptimal decisions.

Additionally, it’s challenging to assess the true impact of business decisions if the data used to guide those decisions is inaccurate. Inaccurate data can distort the outcomes, making it difficult to evaluate the effectiveness of strategies, initiatives, or processes. Ensuring data quality is therefore essential for both effective decision-making and for measuring the success of those decisions over time.

COURSE QUIZ: EXTRACT, TRANSFORM AND LOAD DATA IN POWER BI

1. True or False: The total number of columns that can be used in all the tables within a dataset is restricted to 16,000 columns. This limitation applies to the Power BI service and the datasets used in Power BI Desktop.

True (CORRECT)
False

That is correct! In Power BI Desktop and Power BI Service, the limit for the total number of columns that can be used across all tables is 16,000 columns. This means you cannot exceed this maximum column count when designing and working with your datasets in Power BI. It is important to consider this constraint when structuring your data and designing your data models.

2. True or False: Any data source marked as Beta or Preview has limited support and functionality. So don’t make use of it in production environments.

True (CORRECT)
False

That is correct! Data sources marked as Beta or Preview in Power BI have limited support and functionality. These sources are still in the testing and development phase, and they may not be fully stable or feature-complete. Therefore, it is not recommended to use them in production environments where reliability and robustness are crucial. It’s advisable to wait for these sources to reach general availability before incorporating them into critical workflows or projects.

3. Which of the following item can be considered as an advantage of a local data set?

Governance
Promotion
Scalability
Data Control (CORRECT)

That is correct! Local datasets offer you, as the owner, full control of the data.

4. In which of the following mode(s) does Power BI send a request to the data source and get the result back? Select all that apply:

Dual (CORRECT)
Direct Query (CORRECT)
Import

Exactly! In Dual Mode, Power BI offers the flexibility to combine both DirectQuery and Import Mode capabilities. This approach gives you the benefits of in-memory data (for faster performance with imported data) while still being able to pull real-time data from the source (via DirectQuery). This mix ensures that you can work with both static and dynamic data in a single report, improving efficiency and performance.

In DirectQuery mode, as you mentioned, Power BI queries the underlying data source in real-time whenever a user interacts with the report. This means that every interaction, whether it’s selecting filters, drilling down, or updating visuals, triggers a live query to the data source, ensuring that the data displayed is always up-to-date. While this mode ensures real-time accuracy, it may result in slightly longer response times compared to working with in-memory data. Nonetheless, it’s an essential feature when dealing with large datasets or when real-time data is a critical part of the analysis.

5. As a Data Analyst at Adventure Works, you have been assigned to design an inventory operations system. The data within this system needs to be quantitative, easily searchable, sortable, and suitable for analysis. Which of the following data structures would be the most suitable for fulfilling these requirements?

Unstructured data
Semi structured data
Structured data (CORRECT)

Exactly! Structured data is organized in a highly organized and predefined format, typically in rows and columns, making it easy to search, sort, and analyze. Tools like Microsoft Excel and relational databases such as Azure SQL Database are ideal for storing and working with large volumes of structured data due to their ability to handle well-defined datasets efficiently.

6. You can modify the schedule to fit your needs by choosing the frequency you want the dataset to refresh, such as daily and selecting the time zone you want to use, for example, UTC London.

True (CORRECT)
False

That is correct! By customizing the schedule and selecting the appropriate frequency and time zone, you can ensure that your datasets are refreshed at the desired intervals and in alignment with the relevant time zone.

7. ________ is a Unicode character data string. This can be strings, numbers, or dates represented in a text format.

Binary
Text (CORRECT)
True/False

That is correct! Text is a Unicode character data string. This can be strings, numbers, or dates represented in a text format. The maximum length of this data type can be 536,870,912 bytes or 268,435,456 Unicode characters

8. Which of the following menu items can be used to delete a specific step in Power Query transformations?

Edit Settings
Delete Until End
Delete (CORRECT)

That is correct! Delete option removes the step from the applied steps list and revert the data to the state it was in before that step was applied.

9. ________ rows are instances in a dataset when two or more rows have identical values across all columns.

Empty
Missing
Duplicate (CORRECT)

That is correct! Duplicate rows are instances in a dataset when two or more rows have identical values across all columns. This error often occurs because of data entry errors, glitches within the system, or data that’s been merged from multiple sources.

10. By leveraging Power BI’s ________ functionality, you transform the rows containing individual product categories into separate columns

Pivot (CORRECT)
Unpivot
Transform

That is correct! By leveraging Power BI’s pivot functionality, you transform the rows containing individual product categories into separate columns. This pivot operation enables you to present the sales data in a more concise and structured manner, making it easier to identify trends, top-selling products, and performance within each category.

11. Which of the following menu items let you add rows from one or more tables to another query or table?

Append Queries (CORRECT)
Merge Queries
Combine Files

This process is especially useful when you have similar data spread across different sources or time periods and want to bring it together into a unified dataset. By appending data, you can seamlessly integrate various data sources, enhancing the scope and depth of your analysis. Whether you’re combining sales data from different regions or consolidating data from various departments, appending queries ensures that all relevant information is included for a more complete and accurate report.

12. You import two Microsoft Excel tables named Product and ProductCategory into Power Query.

Product contains the following columns:

ProductID
ProductName
Price
ProductCategoryID
ProductCategory contains following columns:
ProductCategoryID
ProductCategoryName

You want to show the name, price and the name of the category of the products. What should you do?
Choose ProductName, Price and ProductCategoryID from Product table.
MergeProduct and ProductCategory tables by using ProductCategoryID and choose ProductName and Price from Product table and ProductCategoryName field from ProductCategory table. (CORRECT)
Append Product and ProductCategory tables.

To combine the two tables, select the tables to merge, then go to the Home tab in the Power Query Editor ribbon. Click the Merge Queries drop-down menu and select “Merge Queries.” This will open a new window where you can select the Product and ProductCategory tables using the ProductCategoryID. Next, choose the ProductName and Price from the Product table, and the ProductCategoryName field from the ProductCategory table.

13. In ________, all the records from the left table are included in the result set, along with the matching records from the right table.

Left Outer Join (CORRECT)
Full Outer Join
Inner join

In a Left Outer Join, all records from the left table are included in the resulting dataset. Any matching records from the right table are also included. If no matching records exist in the right table, the left table’s records are still included, but the corresponding columns from the right table will contain null values in the result set.

14. You import two Microsoft Excel tables named Customer and City into Power Query. Two tables have a common column named CityID, and City table contains CityName to show the name of the cities. Your manager asked you to create a query that focuses on customers by cities, so in this case you are going to show only the customers who have City data. What should you do in this case?

Use Full Outer Join in the Join Kind dropdown.
Use Left Outer Join in the Join Kind dropdown.
Use Inner Join in the Join Kind dropdown. (CORRECT)

That is correct! An Inner Join operation combines data from two tables based on a common key or condition. It only includes the rows that have matching values in both tables. In other words, it returns only the rows where there is a match between the key values of the first table and the second table. The result set will consist of the matching rows from both tables, while excluding any non-matching rows. This type of join is useful when you want to retrieve data that exists in both tables and perform analysis or operations on the shared data.

15. Full Outer Join operations can potentially return very large result-sets!

True (CORRECT)
False

That is correct! Full Outer Join is used when you want to retrieve all records from both tables, regardless of whether they have matching values in the join condition. So it can potentially return very large result-sets.

16. Which of the following statements can be considered as true?

Numeric joins are more efficient than joins of character strings. (CORRECT)
Numbers are more likely to be incorrect entries
Numbers require more storage space than character strings.

When performing joins in Power BI, it’s generally more efficient to use numeric columns as the matching criteria rather than character strings. Numeric joins involve comparing numerical values, such as integers or decimals, which are processed faster by the underlying database engine. This is because numeric comparisons require less computational overhead compared to string comparisons, where characters need to be evaluated and matched one by one. By using numeric joins, Power BI can optimize the join operation, leading to better performance when combining datasets. Therefore, whenever possible, it’s advisable to use numeric columns as join keys to improve efficiency and speed in Power BI.

17. Which of the following statements about “using short letter codes like state codes for join keys” can be considered as true? Select all that apply:

Costs more storage space when compared to whole numbers.
Almost the same performance when compared to whole number joins. (CORRECT)
Easy to remember and enter (CORRECT)

Using short letter codes, such as state codes, for join keys offers nearly the same performance as whole number joins. This is especially beneficial when dealing with large volumes of data, where performance considerations become crucial.

Additionally, using short letter codes for join keys makes data entry easier and more convenient, as they are simple to remember and input. This convenience can significantly streamline the process of data entry, making it more efficient and user-friendly.

18. Which one is the final stage that brings all the data into the reporting interface, allowing you to filter and visualize the data based on specific criteria?

Transform
Extract
Load (CORRECT)

That is correct! The Load stage is crucial as it brings all the extracted and transformed data into the reporting interface, allowing you to filter and visualize the data based on specific criteria.

19. Which of the following issue can be considered an advantage of using staging in Power BI?

Filtering data
Managing data effectively (CORRECT)
Using advanced transformations

That is correct! By organizing your queries and using staging areas, you manage your data source and your queries effectively.

20. Which of the following count gives you the total number of different values for a column in a dataset?

Unique (CORRECT)
Count
Distinct

That is correct! The term “unique” refers to the count of values within a dataset that occur only once, without any repetition. It represents the total number of distinct values present in the dataset. This measure helps to identify the level of uniqueness in the data and provides insights into the diversity and variability of the values. By understanding the concept of uniqueness, you can gain a deeper understanding of the dataset and its characteristics, allowing for more accurate analysis and decision-making based on the specific values that occur only once.

21. Which of the following count gives you the total number of values that only appear once?

Unique (CORRECT)
Count
Distinct

22. ________ refers to an individual data point or a group of data points that deviates significantly from the remaining data set.

Mode
Outlier (CORRECT)
Standard deviation

That is correct! The formal definition of an outlier in statistics is a data point that significantly deviates from other observations. An outlier refers to an individual data point or a group of data points that deviates significantly from the remaining data set.

23. Which of the following techniques lets you make updates in the master query, and those changes will be automatically applied to the other queries, instead of modifying transformations individually in each query?

Dataflows
Query referencing (CORRECT)
Query duplicating

That is correct! Instead of modifying transformations individually in multiple queries, you can make updates in the master query, and those changes will be automatically applied to all referencing queries. This ensures consistency and makes it easier to maintain and update your data transformations.

24. ________ is designed specifically for data integration and transformation tasks, providing a self-service environment for business users to create and manage ETL (Extract, Transform, Load) processes.

Query reference
Dataflows (CORRECT)
Query duplication

That is correct! Dataflow offer a centralized and scalable approach for data preparation. Dataflow is designed specifically for data integration and transformation tasks, providing a self-service environment for business users to create and manage ETL (Extract, Transform, Load) processes. With data flow, you can connect to various data sources, perform transformations using a visual interface, and store the prepared data in the Power BI service.

25. True or False: You can use dataflows in Microsoft Power BI Desktop and Microsoft Power BI Service.

True (CORRECT)
False

That is correct! You can use dataflows in Microsoft Power BI Desktop and Microsoft Power BI Service. In Power BI Desktop, you can create and manage dataflows using the Power Query Editor. This allows you to connect to various data sources, perform transformations, and define the structure of your data entities. You can then publish these dataflows to the Power BI Service for further use.

26. Which of the following techniques can be used when you want your report users to concentrate on a particular product category?

Dynamic Data Retrieval
Data Transformation
Filters (CORRECT)

That is correct! Parameters can control filters on your data. If you want the viewers of a report to concentrate on a particular product category, you can create a parameter for the product category. This allows the viewer to select the category they’re interested in, and Power BI will adjust the report accordingly.

27. Which of the following features allows you to focus on a specific category of data in your dataset?

Filters (CORRECT)
Data transformation
Dynamic data retrieval

That is correct! Parameters can control filters on your data. For example, if you want the viewers of a report to concentrate on a particular product category, you can create a parameter for the product category. This allows the viewer to select the category they’re interested in, and Power BI will adjust the report accordingly.

28. Which of the following privacy levels allows data to be shared within a group, granting access to authorized users and maintaining data security within the boundaries?

Private
Organizational (CORRECT)
Public

That is correct! The Organizational privacy level in Power BI allows data to be shared within the organization, granting access to authorized users and maintaining data security within the organizational boundaries.

29. As a Data Analyst at Adventure Works, you are given 3 Excel files to import. After you imported the files you need to set the data types correctly in Power Query. What should you do?

Use the automatically set data types per each column.
Check automatically set data types per each column and adjust manually if you notice an inconsistent data type. (CORRECT)
Set each data type manually in Power Query.

That is correct! You must choose appropriate data types for columns to improve performance and data accuracy. Power Query provides automatic data type detection, but it is advisable to review and adjust the inferred data types manually. This prevents incorrect data interpretations and reduces memory consumption.

30. True or False: Direct Query establishes a live connection to the data source, allowing real-time data analysis, while import options load the data into Power BI for offline analysis.

True (CORRECT)
False

That is correct! Power BI supports both Direct Query and import options when connecting to the data sources. You must determine how you want to establish the connection. Direct Query establishes a live connection to the data source, allowing real-time data analysis, while import options load the data into Power BI for offline analysis. Choose the connector that aligns with your connectivity preferences.

31. Which of the following statements about data sources in Power BI is true?

Power BI only supports data sources that are stored in Excel spreadsheets.
Power BI can only connect to data sources that have a specific file format.
Power BI supports both cloud-based and on-premises data sources. (CORRECT)

That is correct! Power BI supports both cloud-based data sources, such as Azure SQL Database, SharePoint Online, and Salesforce, as well as on-premises data sources, such as SQL Server, Oracle, and SharePoint Server.

32. Which of the following best describes a connector in the context of data integration?

A tool which is used to establish a physical connection between two or more data sources. (CORRECT)
A connector is a virtual assistant that helps users navigate through data analysis tasks and provides recommendations based on the input.
A connector is a programming language used to write complex algorithms for data transformation and manipulation.

A connector is a tool that facilitates the connection between different data sources, serving as a bridge for communication and interaction. It handles the technical processes of retrieving data from various sources, providing a standardized interface for accessing and exchanging information. Connectors offer the necessary functionalities, protocols, and drivers to efficiently interact with data sources. They enable data integration, synchronization, and transfer between systems, ensuring seamless data flow and integration across platforms and applications.

33. In ________, data is stored in memory but can also be retrieved from the original data source. This is useful when you are working with dimension tables, which can be queried with fact tables from the same source.

Import mode
Direct Query mode
Dual Mode (CORRECT)

That is correct! In Power BI, data can be stored in memory or retrieved from the original source. This is particularly helpful when working with dimension tables and fact tables from the same source. For example, Adventure Works may have a Sales aggregate by Customer Loyalty table stored in memory for faster query processing. The larger Sales transactions table can be set to DirectQuery mode. By setting the common dimension table, like Date, to Dual mode, report performance can be improved.

34. ________ does not have a predefined structure or format. It is best used for qualitative analysis and usually resides in non-relational databases or unprocessed file formats.

Unstructured data (CORRECT)
Semi-structured data
Structured data

That is correct! Unstructured data does not have a predefined structure or format. It is best used for qualitative analysis anof the join type, only the matching rows from both tables are listed usually resides in non-relational databases or unprocessed file formats. Some examples of this type of data are text documents, audio and video files, social media posts, emails, and images.

35. Which of the following purposes are relevant to the Applied Steps section in the Power Query Editor? Select all that apply:

To undo a step when you make a mistake or change your mind. (CORRECT)
To show the sequence of transformations applied to the selected query (CORRECT)
To preview the data after the applied transformations
To display a list of all the queries in your Power BI project

When you’ve made a mistake or changed your mind and want to undo a transformation, the Applied Steps list makes it easy. To undo a step, simply click the “X” icon next to the step you’d like to remove.

The Applied Steps section in the Power Query Editor is specifically designed to display the sequence of data transformations applied to the selected query. It helps users understand the order of data manipulation and can be used to modify, delete, or reorder steps as needed.

36. Which of the following data errors may cause an inflation in your dataset?

Missing or null values
Inconsistent data types
Duplicate values (CORRECT)

You must ensure that all instances of data duplication are resolved before processing your dataset. If left unresolved, these duplicates can inflate the size of the dataset, which could, in turn, skew your results and lead to inaccurate analysis.

37. You import two Microsoft Excel tables named SalesCompany and SalesOther into Power Query.

SalesCompany contains the following columns:

SalesID
SalesDate
TotalAmount
SalesPersonID

SalesOther contains following columns:

SalesKey
SalesDate
TotalAmount
SalesPersonKey
StoreName

What should you do to append these two tables?

Change the column name SalesKey to SalesID and change the column name SalesPersonKey to SalesPersonID in SalesOther table.
Remove StoreName from SalesOther table.
Remove StoreName from SalesOther, change the column name SalesKey to SalesID and change the column name SalesPersonKey to SalesPersonID in SalesOther table. (CORRECT)

That is correct! Make sure that the tables you will append have an equal number of columns and that the columns have the same names and data types. If you don’t have an equal number of columns or different column names, the extra columns will be added to the most right of the query by preserving their values in the originating query and setting NULL values for the matching new query.

38. You can select ________ to create a new query or table from the merged tables to grow the output horizontally.

Merge Queries as New (CORRECT)
Append Queries
Merge Queries

That is correct! To combine the two tables, select the tables to merge; go to Home on the Power Query Editor ribbon and select the Merge Queries drop-down menu and select Merge Queries as New. This selection opens a new window, where you can select the tables that you want to merge from the dropdown list.

39. In which of the join type, only the matching rows from both tables are listed?

Inner Join (CORRECT)
Full Outer Join
Left Outer Join

That is correct! When you combine both tables with Inner Join, only those that are equal in both tables are listed.

40. If you want to retrieve a result set that includes all records from the matching tables, even if some rows have not matched to the rows in the other tables yet, you can use a ________ between the tables.

Left Outer Join
Full Outer Join (CORRECT)
Inner join

That is correct! A Full Outer Join includes all the rows from both tables, regardless of whether they have a match or not. If a row in one table has no matching row in the other table, it will still be included in the result set, with NULL values for the columns from the non-matching table. This type of join is useful when you want to retrieve a complete set of records from both tables, including any unmatched rows. It ensures that no data is excluded, providing a comprehensive view of the combined data from both tables.

41. As the data ________ process may involve large volumes of data, you carefully monitor the progress to ensure its successful completion

Extraction
Transformation
Loading (CORRECT)

That is correct! As the process of loading data can often involve handling large volumes of information, it is essential to closely monitor its progress to ensure that it completes successfully. By actively observing and tracking the data loading process, you can identify any potential issues or errors that may arise and take necessary actions to address them. Monitoring the data loading process allows you to ensure the accurate and timely ingestion of data, contributing to the overall reliability and integrity of your data analysis tasks.

42. bIn Adventure Works, you receive data from various channels and they cannot be used in their raw form, as they have different formats. You must transform the data and then consolidate it in a unified list. You will only use this data in the ETL process and not show it directly. What should you do?

Use a reference query.
Use a query parameter
Use a staging area (CORRECT)

That is correct! It is appropriate to use a staging area. A staging area serves as an intermediate storage location for raw or unprocessed data, allowing it to be temporarily stored and prepared for further processing.

43. What does Column distribution provide in Power Query?

Column distribution adds distinct and unique amounts below validity information. (CORRECT)
Column distribution provides a comprehensive view of the value distribution within the selected column
Column distribution shows the error and empty row counts and percentages.

That’s correct! Column distribution adds distinct and unique amounts below validity information.

44. Which of the following definitions best describes the term “anomaly”?

Anomaly is the measure of the variability or spread of the data in relation to the mean.
Anomaly is individual data point or a group of data points that deviates significantly from the remaining data set.
An anomaly represents a single point or a group of points that significantly deviate from other points in the broader context or pattern. (CORRECT)

That is correct! An anomaly refers to an individual data point or a cluster of data points that significantly deviate from other points in the broader context or pattern. These anomalies are characterized by their distinctiveness or abnormality compared to the majority of data points, which exhibit similar patterns or behavior. Detecting anomalies is important in data analysis as it helps identify irregularities, outliers, or unexpected patterns that may indicate errors, fraud, or unusual events in the dataset.

45. By establishing a ________, you can establish a connection between an existing query and a new query. Any modifications made to the original query will automatically propagate to the other queries, ensuring consistency and up-to-date information.

duplicate query
dataflow
query reference (CORRECT)

That is correct! By establishing a query reference you can establish a connection between an existing query and a new query. Any modifications made to the original query will automatically propagate to the referenced query, ensuring consistency and up-to-date information.

46. In ________, you use cloud-based processing capabilities, enabling efficient handling of large datasets and complex transformations.

query references
dataflows (CORRECT)
Advanced Editor

That is correct! Power BI dataflows leverage cloud computing for advanced data transformations, handling large volumes efficiently. With cloud-based processing, dataflows perform tasks like extraction, cleansing, and loading (ETL). They offer scalability and parallel processing for optimal performance. Dataflows provide an efficient solution for handling and transforming data in Power BI.

47. Query parameters in Power BI enable _____ retrieval of specific information from a database, allowing you to define _ or conditions to retrieve only the required data. By utilizing query parameters, Power BI generates and executes _____ queries that fetch a smaller, filtered dataset, saving system resources and processing time.

static, predefined, static
dynamic, criteria, dynamic (CORRECT)
manual, filters, manual

That is correct! Query parameters in Power BI enable dynamic retrieval of specific information from a database, allowing you to define criteria or conditions to retrieve only the required data. By utilizing query parameters, Power BI generates and executes dynamic queries that fetch a smaller, filtered dataset, saving system resources and processing time.

48. Which of the following privacy levels allows data to be shared within a group, granting access to authorized users and maintaining data security within the boundaries?

Organizational (CORRECT)
Public
Private

49. By ________ expensive operations in your data transformation pipeline, you can optimize the performance and efficiency of your Power BI queries and calculations.

deferring (CORRECT)
prioritizing
cancelling

That is correct! Expensive operations refer to tasks that require significant computational resources or involve complex calculations, such as merging large datasets, performing complex calculations, or applying multiple transformations on a large number of rows. By deferring these operations until later stages of your data transformation pipeline, you can optimize the performance and efficiency of your Power BI queries and calculations.

50. Data sources for the Power BI service are limited to ________ data sources per user.

100
1000 (CORRECT)
10000

That is correct! Data sources for the Power BI service are limited to 1000 data sources per user.

51. Which of the following connectors are supported in Power BI? Select all that apply:

Google Analytics (CORRECT)
Salesforce (CORRECT)
Excel Online
SQL Server (CORRECT)

You can connect to Google Analytics using the connectors in Power BI.

Salesforce Objects and Salesforce Reports are the available connectors in Power BI.

Power BI supports connecting to SQL Server as an on-premise data source.

52. There are two ways to endorse datasets. makes a dataset available to a broader audience, while is a more selective process that requires special access before other users can view it.

Certification – promotion
Promotion – certification (CORRECT)
Accessibility – promotion

Promotion and certification in Power BI are crucial for recognizing and validating skills and expertise. Promotion involves showcasing achievements by sharing reports, collaborating, and engaging in community activities. Certification provides official recognition from Microsoft for mastering Power BI skills. Both promotion and certification contribute to reputation, credibility, and increased opportunities in data analytics. They foster continuous learning, collaboration, and innovation, empowering users to leverage Power BI for delivering impactful business insights.

53. Due to the many features in Import mode that are not supported in the DirectQuery mode, it’s not possible to switch from Import Mode to Direct Query Mode.

True (CORRECT)
False

That is correct! Switching between Import and DirectQuery modes may not be feasible due to limitations in DirectQuery mode. It lacks support for data transformations, calculated columns, and certain visualizations that rely on pre-aggregated data. Switching modes would require extensive changes to the data model and report design, potentially leading to loss of functionality. It is important to carefully consider the data connectivity mode when setting up your Power BI project, as switching between modes can be complex.

54. Which of the following is required to initiate a workflow and prompt it to run?

Action
Task
Trigger (CORRECT)

That is correct! In Power BI, triggers are key component that initiate a workflow and prompt it to run. They are used to define when and how data should be refreshed or updated in a Power BI dataset. Triggers are vital in ensuring the currency of data within your Power BI reports and dashboards, guaranteeing that they are always updated to reflect the most recent information sourced from the data source.

55. In Power Query, removing a step in the applied steps list may also remove all subsequent steps in the list, as they are dependent on the previous transformations.

True (CORRECT)
False

That is correct! Power Query will automatically revert the data to the state it was in before that step was applied. But you should keep in mind that removing a step may also remove all subsequent steps in the list, as they are dependent on the previous transformations.

56. You have an Excel file that consists of 3 columns, Month, 2022, and 2023. What do you need to do to convert 2022 and 2023 column values to the row values and create extra rows with this way by combinating month-year and values?

Pivot Columns
Group By
Unpivot Columns (CORRECT)

That is correct! Unpivot Column functionality gives you the ability to transform your flat data into a tabular format that presents an aggregated value for each distinct value in a specific column. When working with unstructured flat data, which lacks organization or grouping, it can be challenging to identify patterns within the data due to the absence of structure.

57. Which of the following menu items lets you retrieve all records from both tables, regardless of whether they have matching values in the join condition?

Left Outer Join
Inner Join
Full Outer Join (CORRECT)

A Full Outer Join allows you to retrieve all records from both tables, regardless of whether there are matching values in the join condition. If you want to retrieve a result set that includes all rows from both tables, even if some countries haven’t placed any records yet, you can use a Full Outer Join. This will return a result set that includes all rows from one table along with their corresponding rows from the other table, inserting null values for the rows that do not have a match.

58. True or False: If items do not have appropriate codes, such as product lot or batch numbers or have short life cycles such as order numbers; then choose identification numbers for the join keys.

True (CORRECT)
False

That is correct! There are two recommended options for join keys: identification numbers and short character codes. If items do not have appropriate codes, such as product lot or batch numbers or have short life cycles such as order numbers; then choose identification numbers (random or auto number option might be a good option for generating keys). If an item is referenced repeatedly, you can choose to have short character codes as a join key.

59. ________ provides column statistics such as Minimum, Maximum, Average (Mean), Frequently Occurring Values (Mode), and Standard Deviation and in addition value distribution on the selected column.

Column distribution
Column quality
Column profile (CORRECT)

That is correct! Column profile provides column statistics such as Minimum, Maximum, Average (Mean), Frequently Occurring Values (Mode), and Standard Deviation and in addition value distribution on the selected column. Additionally, column profiling provides a comprehensive view of the value distribution within the selected column, allowing you to understand the spread and frequency of different values. By analyzing these column statistics and value distribution, you can gain a deeper understanding of the data and make informed decisions during data exploration, cleansing, and analysis processes.

60. ________ enable(s) efficient data retrieval and transformation by allowing for dynamic changes, helping you cater to evolving business needs without having to rewrite entire queries.

Parameters (CORRECT)
Dynamic data retrieval
Data transformation

Parameters enable efficient data retrieval and transformation by allowing for dynamic changes, helping you adapt to evolving business needs without having to rewrite entire queries. The more flexible your data analysis tools are, the better equipped you are to meet your organization’s ever-changing demands. This flexibility makes your work more efficient and allows you to provide valuable insights that can guide your company’s decision-making processes.

61. What is the primary benefit of dynamic data retrieval in Power BI?

To enable real-time or near real-time data analysis by fetching the latest data from the source. (CORRECT)
To provide historical data snapshots for reporting purposes.
To store and import large volumes of static data for long-term analysis.

That is correct! The primary benefit of dynamic data retrieval in Power BI is to enable real-time or near real-time data analysis by fetching the latest data from the source. This allows users to stay up-to-date with the most current information and make data-driven decisions based on the most recent data.

62. ________ in Power BI determine the level of data isolation between different data sources and establish secure boundaries for data interaction within your Power BI environment.

Global options for files
Privacy levels (CORRECT)
Data load options

Privacy levels in Power BI define the level of data isolation between different data sources and establish secure boundaries for data interaction within your Power BI environment. These levels are crucial for protecting sensitive data and preventing unauthorized access or data leakage. By setting appropriate privacy levels, you can control the isolation between queries and establish secure boundaries for data interaction. Privacy levels can be configured for each data source based on the sensitivity of the data, ensuring that only authorized users can access and combine data from different sources, thereby maintaining data security and compliance.

63. Which of the connection types will you use when trying to connect to an on-premise SQL Server?

Azure SQL database
SQL Server database (CORRECT)
SQL Server Analysis Services database

That is correct! You choose SQL Server when connecting to an on premise SQL Server instance. If your SQL Server is on cloud then you must change it to Azure SQL Database.

64. Which of the following options best represents the following data?

01st January 2001
Date/Time/Timezone
Date (CORRECT)
Date/Time

65. True or False: The common field between the tables to be merged and used as the key field in the reference table must be unique.

True (CORRECT)
False

66. True or False: Reference query will create a new query which is a copy of the original query and contains all the applied steps of the query.

True
False (CORRECT)

That is correct! You can reference a query by using the Reference option, by right clicking any query in the Queries pane in the left menu. Reference will create a new query which is a copy of the original query but contains just one single step. You can rename the new query as you need and then start to use it.

67. Which of the following items can be considered as a limitation of a dataflow?

Scalability
Data refresh (CORRECT)
Reusability

That is correct! Dataflows have specific refresh limitations, such as the frequency and dependencies on data source availability

68. Which of the following menu items let you combine columns from one or more tables to another query or table?

Merge Queries (CORRECT)
Combine Files
Append Queries

That is correct! Merge queries or tables in Power BI combines one or more tables to another query or table by adding columns together using a common column equality. By merging queries or tables, you can read detailed data from the other tables and show more readable and meaningful data in a single query across tables.

69. Which of the following operations convert any data, which is organized in a wide format with separate columns for each information (region, country etc.), into a long format where the information-specific data is stacked vertically in a single column?

Pivot
Transform
Unpivot (CORRECT)

That is correct! Unpivot operation allows you to convert any data, which is organized in a wide format with separate columns for each information (region, country etc), into a long format where the information-specific data is stacked vertically in a single column. By unpivoting the data, you can easily compare data across different information and gain a holistic view of the overall performance.

70. You can select ________ to create a new query or table from the appended output

Append Queries as New (CORRECT)
Append Queries
Merge Queries

That is correct! You can select Append Queries as New to create a new query or table from the appended output. By choosing the “Append Queries as New” option, you have the ability to create a fresh query or table that incorporates the appended output. This means that the combined result of the appended queries will be treated as a separate entity, allowing you to perform additional transformations or analyses on this new dataset without modifying the original queries. It provides a convenient way to organize and manipulate your data effectively within Power BI.

71. You import two Microsoft Excel tables named Product and ProductCategory into Power Query. Two tables have a common column named ProductCategoryID, and ProductCategory table contains ProductCategoryName to show the name of the categories. You want to show join the tables; but you noticed that some of the products have NULL values for the ProductCategoryID column and you want to show those products also. What should you do in this case?

Use Full Outer Join in the Join Kind dropdown
Use Inner Join in the Join Kind dropdown
Use Left Outer Join in the Join Kind dropdown (CORRECT)

That is correct! Left Outer Join operation displays all rows from the first table and only the matching rows from the second.

72. A join is used to combine information from different sources and integrate data by matching columns, which are considered as ________.

Join types
Join keys (CORRECT)
Join tables

A join is used to combine information from different sources and integrate data by matching columns, known as join keys. By specifying the join keys, Power BI determines how the rows from the different tables should be matched and merged together. This process allows you to integrate data from various sources and perform analysis on the combined dataset, leveraging the relationships between the tables. Joins provide a powerful mechanism for bringing together related information, enabling you to gain insights from interconnected data.

73. ________ serves as an intermediate storage location for raw or unprocessed data, allowing it to be temporarily stored and prepared for further processing .

Staging area (CORRECT)
Applied steps list
Merged queries

That is correct! A staging area serves as an intermediate storage location for raw or unprocessed data, allowing it to be temporarily stored and prepared for further processing. The staging area typically acts as a bridge between the data sources and the data warehouse, where the data is stored.

74. By examining various aspects such as completeness, accuracy, uniqueness, and consistency, ________ enables the identification of potential issues and anomalies within the dataset.

transforming data
extracting data
profiling data (CORRECT)

By examining aspects such as completeness, accuracy, uniqueness, and consistency, data profiling helps identify potential issues and anomalies within the dataset. This proactive approach allows you to make informed decisions about data cleaning, transformation, and enrichment, ultimately improving data quality. Additionally, data profiling aids in effective data exploration and visualization by providing insights into data patterns, relationships, and trends.

75. True or False: Reference queries can contribute to slow data refreshes due to their nature of referencing. When a reference query is refreshed, it needs to ensure that all the referenced queries are also refreshed to maintain data consistency.

True (CORRECT)
False

That is correct! Reference queries can contribute to slow data refreshes due to their nature of referencing. When a reference query is refreshed, it needs to ensure that all the referenced queries are also refreshed to maintain data consistency. This can result in longer refresh times, especially if there are multiple layers of referencing involved.

76. Which of the following techniques allows you to connect to data sources, perform data transformations, and also let you publish to the Power BI Service?

Dataflows (CORRECT)
Reference Queries
Duplicate Queries

That is correct! Dataflows allow you to connect to data sources, perform data transformations, and create business logic to build data entities that can be shared across different reports and dashboards. They can also be published to the Power BI Service and in shared reports and dashboards.

77. True or false: You can use filters when connecting to a database to retrieve specific information, rather than importing the entire dataset and by this way Power BI will only fetch data for that period, saving resources and time.

True
False (CORRECT)

78. You are working in Adventure Works as a data analyst and creating a Power BI project to visualize data. You need to set privacy, data load, and file storage options for all files, and these options may change during your design and development. What should you do?

You can set the options for the current file and repeat it for the other files respectively.
You can set global options for the files when you first configure the environment and you can update the settings later if you need to change anything. (CORRECT)
You can set global options for the files when you first configure the environment and you can not change it after.

That is correct! Remember to regularly review and adjust the global options as your needs change to optimize your Power BI experience. By effectively using these global options, you can streamline your workflow, improve data processing and report generation performance, and gain increased control over data-related tasks.

79. Which of the best practices can increase performance in Power BI?

Maximize the amount of data that needs to be loaded and processed.
Filter and reduce data early in the transformation process. (CORRECT)
Prioritize expensive operations early in the transformation process.

That is correct! To optimize performance, filter and reduce data early in the transformation process. Apply relevant filters and remove unnecessary columns or rows as close to the data source as possible. This minimizes the amount of data that needs to be loaded and processed, improving query performance.

80. You must consider the volume and complexity of your data. Some ________ may perform better with large datasets or have optimizations for specific scenarios.

Authentication mechanisms
Connectors (CORRECT)
Data sources

Feedback: That is correct! You must consider the volume and complexity of your data. Some connectors may perform better with large datasets or have optimizations for specific scenarios. You must evaluate the performance capabilities and scalability of the connectors for your data requirements.

81. Power BI uses scheduled ________ to automate tasks at specified time intervals.

Actions (CORRECT)
Triggers
Tasks

82. True or False: Data types are defined at the row level and the determined by values of each specific row and may differ by rows.

True (CORRECT)
False

That is correct! Data types in Power BI are used to classify values to have a better organized and structured dataset. Data types are defined at the column level and the values contained within a given column are configured to align with the designated data type of the column. Every data type has some specific transformations and options that can be applied.

83. Using join keys prevent difficulties that may arise from typing detailed information such as category, city or gender incorrectly or using a value that could convey the same meaning. In this way, join keys provide a crucial solution for and .

performance, scalability
efficiency, scalability
classification, categorization (CORRECT)

84. As a Data Analyst at Adventure Works, you have been assigned to integrate an external resource to your existing relational database. Before the operation, you need to assess valid, error and empty rows on each column, which allows you to validate your row values for all tables. Which of the following options do you need to use?

Column distribution
Column quality (CORRECT)
Column profile

That is correct! Column quality assessment focuses on ensuring the validity, accuracy, and completeness of data within each column. It involves carefully examining the values present in rows to verify their correctness. By conducting this thorough analysis at the column level, you can identify any invalid entries, errors, or missing values. This detailed process enhances the overall integrity and reliability of the dataset, enabling you to confidently make informed decisions and extract valuable insights from your data analysis.

85. True or False: Local datasets in Power BI allow you to collaborate and create reports based on the same set of data. You can access and use datasets created by others within their organization, without having to create your own datasets from scratch. This ensures consistency in data analysis and reporting and saves time and effort for everyone involved.

True
False (CORRECT)

86. As a Data Analyst at Adventure Works, you have taken over a database from one of your suppliers to integrate it into the Adventure Works relational database. However, you have noticed that the data in the source is not well organized, and some columns contain different types of data within a single column. What are the potential issues that may arise in this situation?

Missing values
Inconsistent data types (CORRECT)
Duplicate values

That is correct! Identifying and resolving inconsistent data types within your Power BI dataset is crucial. Failing to address these inconsistencies can result in calculations behaving improperly, leading to errors in the generated results. It is essential to ensure data type consistency to maintain accurate and reliable data analysis in Power BI.

87. Which of the following menu items lets you retrieve the results from the left table, along with the matching records from the right table?

Left Outer Join (CORRECT)
Inner Join
Full Outer Join

That is correct! Full Outer Join lets you retrieve all records from both tables, regardless of whether they have matching values in the join condition. If you want to retrieve a result set that includes all rows between tables, even if some countries haven’t placed in any records yet, you can use a Full Outer Join between the tables. This will provide a result set that includes all rows from one table and their corresponding rows from the other table, with Null values for the ones that do not match.

CONCLUSION – Graded Assessment and Course wrap up

In conclusion, this module’s comprehensive assessment serves as a vital benchmark for evaluating your mastery of the key skills and knowledge areas covered throughout the course. Through practical exercises, projects, and examinations, you will have the opportunity to demonstrate your proficiency and understanding, ensuring you’re well-prepared for more advanced topics or real-world applications in your field. This assessment not only reinforces your learning but also offers valuable feedback, highlighting your progress and areas that may need further refinement, ultimately contributing to your overall development and readiness for future challenges.