[Microsoft] PL-300 - Power BI Data Analyst Associate Exam Dumps & Study Guide

Free [Microsoft] PL-300 - Power BI Data Analyst Associate Practice Questions Preview

• Question 1

  HOTSPOT -
  You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.)
  
  The data has the following refresh requirements:
  ✑ Customer must be refreshed daily.
  ✑ Date must be refreshed once every three years.
  ✑ Sales must be refreshed in near real time.
  ✑ SalesAggregate must be refreshed once per week.
  You need to select the storage modes for the tables. The solution must meet the following requirements:
  ✑ Minimize the load times of visuals.
  ✑ Ensure that the data is loaded to the model based on the refresh requirements.
  Which storage mode should you select for each table? To answer, select the appropriate options in the answer area.
  NOTE: Each correct selection is worth one point.
  Hot Area:
  
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer. The optimal storage modes for the tables, considering the refresh requirements and the need to minimize visual load times, are as follows:
  

  • Customer: Dual - The Customer table should be set to Dual storage mode.
    Reasoning: Since the Customer table is a dimension table related to both the Sales (DirectQuery) and SalesAggregate (Import) fact tables, using Dual storage mode allows Power BI to efficiently determine whether to retrieve the data from the cache or directly from the source depending on the query context. This balances near real-time needs for Sales data with aggregated data from SalesAggregate.
    Citation: Storage mode in Power BI Desktop - Power BI
  • Date: Dual - The Date table should also be set to Dual storage mode.
    Reasoning: Similar to the Customer table, the Date table is a dimension table related to both the Sales and SalesAggregate fact tables. Dual storage mode ensures optimal performance by allowing Power BI to use cached data when interacting with the SalesAggregate table and direct queries when interacting with the Sales table.
    Citation: Storage mode in Power BI Desktop - Power BI
  • Sales: DirectQuery - The Sales table should use DirectQuery storage mode.
    Reasoning: Because the Sales table requires near real-time data, DirectQuery is the most appropriate choice. DirectQuery retrieves data directly from the source each time a query is executed, ensuring that the visuals always display the latest information.
    Citation: Storage mode in Power BI Desktop - Power BI
  • SalesAggregate: Import - The SalesAggregate table should use Import storage mode.
    Reasoning: Since the SalesAggregate table is refreshed only once per week, Import mode is suitable. Importing the data allows for faster query response times and improved visual load times because the data is stored in the Power BI model's cache.
    Citation: Storage mode in Power BI Desktop - Power BI

  Regarding the alternative suggestions mentioned in the discussion:

  • If both Sales and SalesAggregate required near real-time data, then DirectQuery would be appropriate for both. However, since SalesAggregate is only refreshed weekly, Import mode is a better option for performance.
  • While Customer and Date could potentially be set to Import mode, using Dual mode provides more flexibility and optimization, particularly when dealing with tables in DirectQuery mode.

  
  

  In summary, the suggested answer aligns with best practices for Power BI storage modes, ensuring that data is loaded based on refresh requirements while minimizing visual load times.

  
  

  The storage modes ensure that Sales data is near real-time using DirectQuery, SalesAggregate is performant using Import, and dimension tables efficiently support both using Dual mode.

  • Citations:
  • Storage mode in Power BI Desktop - Power BI, https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

• Question 2

  You have a project management app that is fully hosted in Microsoft Teams. The app was developed by using Microsoft Power Apps.
  You need to create a Power BI report that connects to the project management app.
  Which connector should you select?
  

  • A. Microsoft Teams Personal Analytics
  • B. SQL Server database
  • C. Dataverse
  • D. Dataflows

  Correct Answer: C

  Explanation:

  The AI agrees with the suggested answer.
  The recommended answer is C. Dataverse.
  Reasoning:
  Since the Power Apps application is fully hosted in Microsoft Teams and you need to create a Power BI report that connects to it, Dataverse is the most appropriate connector.
  Dataverse is the data storage service that Power Apps uses to store and manage data. When a Power Apps application is built, its data is typically stored in Dataverse. Therefore, to connect Power BI to the data of the Power Apps application, you should use the Dataverse connector.
  
  Reasons for not choosing other options:
  

  • A. Microsoft Teams Personal Analytics: This provides analytics about your own usage of Teams, not the data stored within a Power Apps application in Teams.
  • B. SQL Server database: While Power Apps can connect to SQL Server, it's unlikely that the app *only* uses SQL Server *if* it's built within the Power Platform ecosystem; the default and most integrated storage is Dataverse. If the Power Apps application *did* use SQL Server, you would have that connection information available, and the question does not suggest an external SQL Server is being used.
  • D. Dataflows: Dataflows are a data preparation technology, not a direct connector to an application's data store. While you *could* potentially use a dataflow to extract data from Dataverse, connecting directly to Dataverse from Power BI is the more direct and appropriate approach.

  • Dataverse, https://learn.microsoft.com/en-us/power-platform/dataverse/
  • Power BI Dataverse connector, https://learn.microsoft.com/en-us/power-bi/connect-data/powerbi-dataverse-connector

• Question 3

  For the sales department at your company, you publish a Power BI report that imports data from a Microsoft Excel file located in a Microsoft SharePoint folder.
  The data model contains several measures.
  You need to create a Power BI report from the existing data. The solution must minimize development effort.
  Which type of data source should you use?
  

  • A. Power BI dataset
  • B. a SharePoint folder
  • C. Power BI dataflows
  • D. an Excel workbook

  Correct Answer: A

  Explanation:

  The AI agrees with the suggested answer of A (Power BI dataset).
  Reasoning: The question explicitly states the need to minimize development effort and reuse the existing data model and measures. Connecting to a Power BI dataset (now referred to as semantic model) allows you to leverage the existing data model, including all the measures already defined. This avoids the need to redefine the data connections, transformations, and measures, thus minimizing development effort.
  Why other options are not ideal:

  • B. SharePoint folder: Connecting directly to the SharePoint folder would require rebuilding the data model and measures from scratch, negating the "minimize development effort" requirement.
  • C. Power BI dataflows: While dataflows are useful for data preparation and sharing, they don't directly address the reuse of existing measures defined in the original Power BI report. Using a dataflow would still require creating a new dataset and measures in the new report.
  • D. Excel workbook: Similar to connecting to the SharePoint folder, connecting directly to the Excel workbook would require rebuilding the data model and measures, failing to minimize development effort.

  Therefore, using the Power BI dataset is the most efficient way to create a new report based on the existing data model and measures.

• Question 4

  You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns:
  ✑ Customer ID
  ✑ Customer Name
  ✑ Phone
  ✑ Email Address
  ✑ Address ID
  Address contains the following columns:
  ✑ Address ID
  ✑ Address Line 1
  ✑ Address Line 2
  ✑ City
  ✑ State/Region
  ✑ Country
  ✑ Postal Code
  Each Customer ID represents a unique customer in the Customer table. Each Address ID represents a unique address in the Address table.
  You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer.
  What should you do?
  

  • A. Merge the Customer and Address tables.
  • B. Group the Customer and Address tables by the Address ID column.
  • C. Transpose the Customer and Address tables.
  • D. Append the Customer and Address tables.

  Correct Answer: A

  Explanation:

  The AI agrees with the suggested answer, which is A: Merge the Customer and Address tables.
  
  Reasoning:
  The problem requires combining data from two tables (Customer and Address) into a single table with one row per customer, including address details (City, State/Region, and Country). The Customer table contains an 'Address ID' that links to the Address table. The correct way to achieve this is by merging the two tables using the 'Address ID' as the common key.
  Merging the tables is analogous to performing a JOIN operation in SQL. This operation combines rows from two or more tables based on a related column between them. In this scenario, merging Customer and Address tables on 'Address ID' will add the required address columns (City, State/Region, Country) to the Customer table, effectively creating a single table with all the necessary information for each customer.
  
  Reasons for not choosing the other options:
  

  • B. Grouping: Grouping is used to aggregate data within a single table, not to combine data from different tables. Grouping the Customer and Address tables by Address ID would not produce the desired output of one row per customer with address details.
  • C. Transposing: Transposing is used to switch rows and columns, which is not relevant to combining data from two tables based on a common key. Transposing the Customer and Address tables would not solve the problem.
  • D. Appending: Appending is used to stack two tables on top of each other, which is only suitable when the tables have the same columns and structure. Appending Customer and Address tables would result in a table with combined customer and address records, but not in the desired format of one row per customer with corresponding address information.

  
  Citations:

  • Power BI Merge queries overview, https://learn.microsoft.com/en-us/power-query/merge-queries-overview

• Question 5

  HOTSPOT -
  You have two Azure SQL databases that contain the same tables and columns.
  For each database, you create a query that retrieves data from a table named Customer.
  You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com.
  What should you do? To answer, select the appropriate options in the answer area.
  NOTE: Each correct selection is worth one point.
  Hot Area:
  
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer.
  The question requires combining two Customer tables from two Azure SQL databases into a single table, while minimizing the data model size and supporting scheduled refresh in powerbi.com. The suggested answer achieves this by using "Append Queries as New" and "Disable loading the query to the data model".
  Reasoning:
  

  • Append Queries as New: This option correctly combines the data from the two Customer tables into a new query. Appending is the correct method for combining tables with the same columns but different rows. Using 'Append Queries as New' ensures that a new combined table is created, leaving the original tables untouched.
  • Disable loading the query to the data model: This option is crucial for minimizing the data model size. By disabling the loading of the original queries (the individual Customer tables) into the data model, only the combined table is loaded, which avoids duplicating the data and saves memory. The appended query will be loaded to the model. Although the original queries are not loaded into the model, they will still be refreshed during the scheduled refresh, ensuring the combined table contains the latest data.

  Why not the other options:
  Other options like merging queries or enabling loading for all queries would either not combine the tables correctly (merging is for combining columns, not rows) or would lead to a larger data model (loading all queries), which contradicts the question's requirements.
  The comments in the discussion also suggest that using "Append Queries" instead of "Append Queries as New" could potentially cause issues with the second option ("Disable loading into the model") because the disable load action might need to be performed on both tables.

  In summary, the suggested answer is the optimal solution because it effectively combines the data from the two tables, minimizes the data model size, and supports scheduled refresh.

• Question 6

  DRAG DROP -
  In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.
  Every Product has a ProductSubCategory.
  Not every ProductsubCategory has a parent ProductCategory.
  You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query.
  How should you merge the tables? To answer, drag the appropriate merge types to the correct queries. Each merge type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
  NOTE: Each correct selection is worth one point.
  Select and Place:
  
  

  Correct Answer: See interactive view.

  Explanation:

  The AI assistant agrees with the suggested answer.
  Here's a detailed explanation:
  The question requires merging three queries (ProductCategory, ProductSubCategory, and Product) in Power Query Editor while ensuring optimal performance. The relationships are: Every Product has a ProductSubCategory, and not every ProductSubCategory has a parent ProductCategory.
  Box 1: Inner Join

  • Reasoning: The relationship "Every Product has a ProductSubCategory" indicates that each row in the Product table *must* have a corresponding entry in the ProductSubCategory table. An inner join is the most efficient choice in this scenario because it only returns rows where there is a match in both tables. Since *every* Product has a SubCategory, there's no need to retain non-matching rows, making the inner join the optimal performer here.
  • Why other options are not suitable: Left Outer Join would also work functionally, as it would keep all rows from the 'Product' table and matching rows from 'ProductSubCategory'. Since every Product has a ProductSubCategory in this case, the result would be the same as an Inner Join. However, Inner Join is specifically optimized for cases where matching records are expected and thus will be more performant. Right Outer and Full Outer joins are not appropriate because they would include rows from ProductSubCategory that do not have corresponding entries in Product, which is not what we want when joining 'Product' and 'ProductSubCategory' tables where every 'Product' record *must* have a related 'ProductSubCategory' record.

  Box 2: Left Outer Join

  • Reasoning: The relationship "Not every ProductSubCategory has a parent ProductCategory" implies that some subcategories might not be associated with any category. A left outer join from ProductSubCategory to ProductCategory is the right choice because it keeps all rows from the ProductSubCategory table (the left table) and only the matching rows from the ProductCategory table (the right table). This ensures that all subcategories are retained, even those without a parent category.
  • Why other options are not suitable: An inner join would exclude ProductSubCategories that do not have a ProductCategory, which violates the requirement to include all ProductSubCategories. A Right Outer join would only keep the records of ProductCategory and matching ProductSubCategory, while the question requires to include all ProductSubCategory. A Full Outer Join will also work functionally, however, is not the most performant and optimized join in this case.

  • Citation 1: Merge queries overview, https://docs.microsoft.com/en-us/power-query/merge-queries-overview
  • Citation 2: Merge queries inner, https://docs.microsoft.com/en-us/power-query/merge-queries-inner
  • Citation 3: Merge queries left outer, https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer

• Question 7

  You are building a Power BI report that uses data from an Azure SQL database named erp1.
  You import the following tables.
  
  You need to perform the following analyses:
  ✑ Orders sold over time that include a measure of the total order value
  Orders by attributes of products sold
  
  The solution must minimize update times when interacting with visuals in the report.
  What should you do first?
  

  • A. From Power Query, merge the Order Line Items query and the Products query.
  • B. Create a calculated column that adds a list of product categories to the Orders table by using a DAX function.
  • C. Calculate the count of orders per product by using a DAX function.
  • D. From Power Query, merge the Orders query and the Order Line Items query.

  Correct Answer: D

  Explanation:

  The AI assistant agrees with the suggested answer (D).
  Reasoning: The optimal approach to minimize update times in Power BI visuals, especially when dealing with order and product data, involves creating an efficient data model. Merging the "Orders" and "Order Line Items" queries in Power Query directly addresses the need to calculate total order value and analyze orders by product attributes. This merging process consolidates the necessary data into a single table, reducing the complexity of calculations and relationships that Power BI needs to manage. Consequently, this approach leads to faster rendering and update times for visuals in the report.
  
  Reasons for not choosing the other options:

  • Option A: While merging "Order Line Items" and "Products" is useful for understanding product attributes within order line items, it doesn't directly address the primary requirement of analyzing orders over time and their total value. It also creates a less efficient model if the goal is to analyze orders, as it would still require relating this merged table back to the "Orders" table.
  • Option B: Creating a calculated column to add a list of product categories to the "Orders" table using DAX is not efficient. DAX calculated columns are computed during data refresh and consume memory, which can slow down report performance, especially with large datasets. This approach is also less flexible than merging queries in Power Query.
  • Option C: Calculating the count of orders per product using a DAX function is also not the most efficient approach. Similar to calculated columns, DAX measures are calculated at query time, but this specific calculation doesn't directly contribute to the overall goal of analyzing orders over time and their total value. Furthermore, it adds complexity to the data model without streamlining the primary analysis requirements.

  By merging the "Orders" and "Order Line Items" queries, the data model becomes more streamlined, calculations are simplified, and the overhead of handling multiple tables and relationships is reduced, ultimately leading to faster update times in reports.

  
  Citations:

  • Power BI documentation on data modeling: https://learn.microsoft.com/en-us/power-bi/guidance/
  • Power Query documentation: https://learn.microsoft.com/en-us/power-query/

• Question 8

  You have a Microsoft SharePoint Online site that contains several document libraries.
  One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure.
  You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis.
  What should you do?
  

  • A. Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.
  • B. Get data from a SharePoint list and enter the site URL. Select Combine & Transform, then filter by the folder path to the manufacturing reports library.
  • C. Get data from a SharePoint folder, enter the site URL, and then select Combine & Load.
  • D. Get data from a SharePoint list, enter the site URL, and then select Combine & Load.

  Correct Answer: A

  Explanation:

  The AI assistant agrees with the suggested answer A.
  Reasoning:
  The problem requires loading manufacturing reports (Excel files) from a specific document library within a SharePoint Online site into Power BI Desktop for analysis. The crucial step is to ensure that only the manufacturing reports are loaded and that the data is properly structured for analysis.
  Option A suggests using the "SharePoint folder" connector and then filtering the data using "Transform" to specify the path of the manufacturing reports library. This method is correct because:

  • It uses the "SharePoint folder" connector, which is designed to connect to and retrieve files from a SharePoint document library (which is essentially a folder).
  • It employs the "Transform" option, enabling the user to filter the data based on the folder path, thus ensuring that only the Excel files from the manufacturing reports library are loaded.

  Why other options are incorrect:
  

  • Option B: "SharePoint list" is incorrect because it is used to connect to SharePoint lists, not document libraries (folders containing files).
  • Option C: "Combine & Load" without a filtering step will load all the files from the entire SharePoint site (or the top-level folder), not just the manufacturing reports. This approach does not fulfill the requirement to load only the manufacturing reports.
  • Option D: "SharePoint list" is incorrect for the same reason as option B, and "Combine & Load" is unsuitable because there is no filtering according to question.

  Therefore, option A is the most appropriate method because it uses the correct connector ("SharePoint folder") and includes the essential filtering step to load only the required Excel files from the manufacturing reports library.

  Suggested Answer: A

  Explanation:

  • A. Get data from a SharePoint folder and enter the site URL. Select Transform, then filter by the folder path to the manufacturing reports library.: This is the correct approach. It connects to the SharePoint folder, and then allows you to filter to the specific folder containing the manufacturing reports.
  • B. Get data from a SharePoint list and enter the site URL. Select Combine & Transform, then filter by the folder path to the manufacturing reports library.: Incorrect. SharePoint lists are for structured data, not files.
  • C. Get data from a SharePoint folder, enter the site URL, and then select Combine & Load.: Incorrect. Combine & Load will attempt to combine all files in the SharePoint folder without filtering, which is not what the question asks for.
  • D. Get data from a SharePoint list, enter the site URL, and then select Combine & Load.: Incorrect. SharePoint lists are for structured data, not files, and Combine & Load doesn't allow for filtering to the specific folder.

  
  Citations:

  • Connect to SharePoint folders in Power BI Desktop, https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-to-sharepoint-folder

• Question 9

  DRAG DROP -
  You have a Microsoft Excel workbook that contains two sheets named Sheet1 and Sheet2.
  Sheet1 contains the following table named Table1.
  
  Sheet2 contains the following table named Table2.
  
  You need to use Power Query Editor to combine the products from Table1 and Table2 into the following table that has one column containing no duplicate values.
  
  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
  Select and Place:
  
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer.
  The correct sequence of actions is as follows:

  • Import the data from Excel, and select Table1 and Table2.
  • Append Table2 to Table1.
  • Select Table1, and then select Remove duplicates.

  Reasoning:
  The objective is to combine product lists from two Excel tables, eliminate duplicates, and load it into Power BI using Power Query Editor. The suggested steps accomplish this in a logical and efficient manner.

  • Import the data from Excel, and select Table1 and Table2: This step is essential because it brings the data from the Excel workbook into Power Query Editor, which is a prerequisite for any further transformations.
  • Append Table2 to Table1: Appending combines the rows from Table2 into Table1, effectively creating a single list of products from both tables. This is the correct way to combine the two tables.
    
  • Select Table1, and then select Remove duplicates: After appending, the combined table (Table1) will likely contain duplicate product names. This step ensures that only unique product names remain in the final output, fulfilling the requirement of having no duplicate values.

  Why other sequences are incorrect:
  Any other sequence of these steps would not produce the desired result:

  • Removing duplicates before appending would only remove duplicates within each original table, not across both tables after they are combined.
    
  • Trying to append before importing the data is impossible as the tables need to be loaded into Power Query first.

  This approach correctly addresses the problem by first importing the data, combining the tables through appending, and then ensuring data quality by removing duplicates.

  Citations:

  • Microsoft Power BI Documentation, https://learn.microsoft.com/en-us/power-bi/
  • Power Query Documentation, https://learn.microsoft.com/en-us/power-query/

• Question 10

  You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.
  You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.
  What should you do?
  

  • A. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
  • B. Change the data type of the Logged column to Date.
  • C. Split the Logged column by using at as the delimiter.
  • D. Apply a transformation to extract the first 11 characters of the Logged column.

  Correct Answer: C

  Explanation:

  The AI assistant suggests that option D is the most appropriate answer.
  
  The reasoning is that extracting the first 10 characters ("YYYY-MM-DD") from the "Logged" column provides a standard date format that Power BI can readily recognize and convert to a Date data type. This conversion is essential to leverage Power BI's built-in date hierarchy features for analysis.
  
  Here's a breakdown of why other options are less suitable:

  • Option A: Extracting the last 11 characters is incorrect because it would extract the time portion ("at HH:MM"), which is not relevant for date-based analysis and cannot be directly converted to the Date data type.
  • Option B: Simply changing the data type to Date might fail because the original format "YYYY-MM-DD at HH:MM" is not a standard date format that Power BI automatically recognizes. A transformation is needed first.
  • Option C: Splitting the column by the "at" delimiter would separate the date and time, but the resulting date column would still be in text format. While this is a viable way, it doesn't directly solve the main goal, which is to apply the built-in date hierarchy. Also the split step is not necessary, since extracting the first 10 digits are enough.

  Therefore, option D provides the most efficient and direct path to enabling date hierarchy analysis.

  
  Citations:

  • Power BI Data Types, https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types
  • Power BI Date Hierarchy, https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-table-auto