PL 300 Exam | Questions and Answers (Complete Solutions)

PL 300 Exam | Questions and Answers (Complete Solutions) 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 refreshe ...

PL 300 Exam | Questions and Answers (Complete Solutions) 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. Customer: Direct Query, Dual, Import Date: DirectQuery, Dual, Import Sales: DirectQuery, Dual, Import SalesAggregate: DirectQuery, Dual, Import Customer: Dual Date: Dual Sales: DirectQuery SalesAggregate: Import 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 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 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. 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. Option to use to combine the Customer tables: Append queries, Append queries as new, Merge queries, Merge queries as new Action to perform on the original two SQL database queries: Delete the queries, Disable including the query in the report refresh, Disable loading the query to the data model, Duplicate the queries Option to use to combine the Customer tables: Append queries as new Action to perform on the original two SQL database queries: Disable loading the query to the data model 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: JOIN KINDS: Full outer Inner Left anti Left outer Right anti Right outer Product - Product Subcategory : Inner ProductSubCategory - ProductCategory : Left outer 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. 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. 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: From Power Query Editor, remove errors from the table; From Power Query Editor, select Table1, and then select Remove Duplicates; From Power Query Editor, merge Table1 and Table2; From Power BI Desktop, import the data from excel, and select Table1 and Table2; From Power Query Editor, append Table2 to Table1 From Power Query Editor, append Table2 to Table1 From Power Query Editor, remove errors from the table From Power Query Editor, select Table1, and then select Remove Duplicates 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. You have a Microsoft Excel file in a Microsoft OneDrive folder. The file must be imported to a Power BI dataset. You need to ensure that the dataset can be refreshed in powerbi.com. Which two connectors can you use to connect to the file? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. A. Excel Workbook B. Text/CSV C. Folder D. SharePoint folder E. Web You are profiling data by using Power Query Editor. You have a table named Reports that contains a column named State. The distribution and quality data metrics for the data in State is shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Hot Area: There are [answer] different values in State including nulls There are [answer] non-null values that occur only once in state 69 4 You have two CSV files named Products and Categories. The Products file contains the following columns: ✑ ProductID✑ ProductName✑ SupplierID✑ CategoryID The Categories file contains the following columns: ✑ CategoryID✑ CategoryName✑ CategoryDescription From Power BI Desktop, you import the files into Power Query Editor.You need to create a Power BI dataset that will contain a single table named Product. The Product will table includes the following columns: ✑ ProductID✑ ProductName✑ SupplierID✑ CategoryID✑ CategoryName✑ CategoryDescription How should you combine the queries, and what should you do on the Categories query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Combine the queries by performing a: Append, Merge, Transpose On the categories query: Delete the query, Disable the query load, Exclude the query from report refresh Combine the queries by performing a: Merge On the categories query: Disable the query load You have an Azure SQL database that contains sales transactions. The database is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection? A. Add a SQL statement. B. Set the Command timeout in minutes setting. C. Set Data Connectivity mode to Import. D. Set Data Connectivity mode to DirectQuery. DRAG DROP -You have a folder that contains 100 CSV files. You need to make the file metadata available as a single dataset by using Power BI. The solution must NOT store the data of the CSV files. 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: From Power BI desktop, select Get Data, and then select folder; From Power Query Editor, expand the Attributes column; From Power Query Editor, remove the content column; From Power Query Editor, remove the attributes column; From Power BI desktop, select Get Data, and then select Text/CSV; From Power Query Editor, combine the content column From Power BI desktop, select Get Data, and then select folder From Power Query Editor, expand the Attributes column From Power Query Editor, combine the content column A business intelligence (BI) developer creates a dataflow in Power BI that uses DirectQuery to access tables from an on-premises Microsoft SQL server. The Enhanced Dataflows Compute Engine is turned on for the dataflow. You need to use the dataflow in a report. The solution must meet the following requirements: ✑ Minimize online processing operations. ✑ Minimize calculation times and render times for visuals. ✑ Include data from the current year, up to and including the previous day.What should you do? A. Create a dataflows connection that has DirectQuery mode selected. B. Create a dataflows connection that has DirectQuery mode selected and configure a gateway connection for the dataset. C. Create a dataflows connection that has Import mode selected and schedule a daily refresh. D. Create a dataflows connection that has Import mode selected and create a Microsoft Power Automate solution to refresh the data hourly. DRAG DROP-You publish a dataset that contains data from an on-premises Microsoft SQL Server database. The dataset must be refreshed daily. You need to ensure that the Power BI service can connect to the database and refresh the dataset. Which four actions should you perform in sequence? Add the dataset owner to the data source; Configure an on-premises data gateway; Configure a virtual network data gateway; Add a data source; Configure a scheduled refresh Configure an on-premises data gateway Add a data source Add the dataset owner to the data source Configure a scheduled refresh You attempt to connect Power BI Desktop to a Cassandra database. From the Get Data connector list, you discover that there is no specific connector for the Cassandra database. You need to select an alternate data connector that will connect to the database. Which type of connector should you choose? A. Microsoft SQL Server database B. ODBC C. OLE DB D. OData You receive annual sales data that must be included in Power BI reports.From Power Query Editor, you connect to the Microsoft Excel source shown in the following exhibit. You need to create a report that meets the following requirements: • Visualizes the Sales value over a period of years and months • Adds a slicer for the month • Adds a slicer for the year Which three actions should you perform in sequence? Select the Month and MonthNumber Columns; Select Unpivot other columns; Rename the attribute column as Year and the Value column as sales; Select the 2019, 2020, 2021 columns; Select Transpose Select the Month and MonthNumber Columns Select Unpivot other columns Rename the attribute column as Year and the Value column as sales You are using Power BI Desktop to connect to an Azure SQL database.The connection is configured as shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.NOTE: Each correct solution is worth one point. The default timeout for the connection from PowerBI Desktop to the database will be - Unlimited, one minute, 10 minutes The navigator will display - all the tables, only tables that contain data, only tables that contain hierarchies The default timeout for the connection from PowerBI Desktop to the database will be - 10 minutes The navigator will display - only tables that contain data You are creating a report in Power BI Desktop. You load a data extract that includes a free text field named coll. You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model. What should you do? A. In the report, add a DAX calculated column that calculates the length of model. B. In the report, add a DAX function that calculates the average length of col1 C. From Power Query Editor, add a column that calculates the length of col1 D. From Power Query Editor, change the distribution for the Column profile to group by length for col1 You have a collection of reports for the HR department of your company. The datasets use row-level security (RLS). The company has multiple sales regions. Each sales region has an HR manager. You need to ensure that the HR managers can interact with the data from their region only. The HR managers must be prevented from changing the layout of the reports. How should you provision access to the reports for the HR managers? A. Publish the reports in an app and grant the HR managers access permission. B. Create a new workspace, copy the datasets and reports, and add the HR managers as members of the workspace. C. Publish the reports to a different workspace other than the one hosting the datasets. D. Add the HR managers as members of the existing workspace that hosts the reports and the datasets. You need to provide a user with the ability to add members to a workspace. The solution must use the principle of least privilege. Which role should you assign to the user? A. Viewer B. Admin C. Contributor D. Member You have a Power BI query named Sales that imports the columns shown in the following table. Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis. You need to reduce the load times of the query without affecting the analysis. Which two actions achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. A. Remove the rows in which Sales[Status] has a value of Canceled. B. Remove Sales[Sales_Date]. C. Change the data type of Sale[Delivery_Time] to Integer. D. Split Sales[Sale_Date] into separate date and time columns. E. Remove Sales[Canceled Date]. You build a report to analyze customer transactions from a database that contains the tables shown in the following table. You import the tables. Which relationship should you use to link the tables? A. one-to-many from Transaction to Customer B. one-to-one between Customer and Transaction C. many-to-many between Customer and Transaction D. one-to-many from Customer to Transaction You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned. You build a report analyzing the internal networks of employees based on whom they send emails to. You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size. What should you do? A. From Model view, set the Subject and Body columns to Hidden. B. Remove the Subject and Body columns during the import. C. Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent. You create a Power BI dataset that contains the table shown in the following exhibit. You need to make the table available as an organizational data type in Microsoft Excel. How should you configure the properties of the table? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Row label: Cost center, Headcount, ID, Name Key Column: Cost center, Headcount, ID, Name Is Featured Label: Yes, No Row label: Cost center Key Column: ID Is Featured Label: Yes You have the Power BI model shown in the following exhibit. A manager can represent only a single country.You need to use row-level security (RLS) to meet the following requirements: ✑ The managers must only see the data of their respective country. ✑ The number of RLS roles must be minimized. Which two actions should you perform? Each correct answer presents a complete solution.NOTE: Each correct selection is worth one point. A. Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function. B. Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function. C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions. D. Create one role for each country. E. For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single. You have a Power BI imported dataset that contains the data model shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.NOTE: Each correct selection is worth one point.Hot Area: Changing the [answer] setting of the relationships will improve report query performance. : Cardinality, Cross filter direction, Assume Referential Integrity The data model is organized into a [answer] : star schema, snowflake schema, denormalized table Changing the [answer] setting of the relationships will improve report query performance. : Assume Referential Integrity The data model is organized into a [answer] : star schema You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales.You need to create a measure that calculates the total sales from the equivalent month of the previous year.How should you complete the calculation? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point. Sales Previous Year = CALCULATE, EVALUATE, SUM, SUMX [Total Sales], DATESMTD, PARALLELPERIOD, SAMEPERIODLASTYEAR, TOTALMTD [Date], 'Date' [Date], 'Date' [Month] Sales Previous Year = CALCULATE [Total Sales], PARALLELPERIOD 'Date' [Month] You plan to create a report that will display sales data from the last year for multiple regions. You need to restrict access to individual rows of the data on a per region-basis by using roles. Which four 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: Publish the report Assign users to the role Add a filter to the report Create a role definition Import the data to PowerBI desktop Import the data to PowerBI desktop Create a role definition Assign users to the role Publish the report You create a data model in Power BI.Report developers and users provide feedback that the data model is too complex.The model contains the following tables. The model has the following relationships: ✑ There is a one-to-one relationship between Sales_Region and Region_Manager. ✑ There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager. ✑ There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager. You need to denormalize the model into a single table. Only managers who are associated to a sales region must be included in the reports.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. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.Select and Place: Merge [Region_Manager] and [Manager] by using an inner join Merge [Sales_Manager] and [Sales_Region] by using a left join Merge [Sales_Region] and [Sales_Manager] by using an inner join Merge [Sales_Region] and [Sales_Manager] by using an inner join as a new query named [Sales_Region_and_Manager] Merge [Sales_Region] and [Region_Manager] by using a right join as a new query named [Sales_Region_and_Region_Manager] Merge [Sales_Region] and [Region_Manager] by using an inner join Merge [Sales_Region] and [Sales_Manager] by using an inner join Merge [Region_Manager] and [Manager] by using an inner join Merge [Sales_Region] and [Region_Manager] by using a right join as a new query named [Sales_Region_and_Region_Manager] You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in shared capacity of powerbi.com.The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 08:00 and 17:00.The report is a single page that contains 15 AppSource visuals and 10 default visuals.Users say that the report is slow to load the visuals when they access and interact with the report.You need to recommend a solution to improve the performance of the report.What should you recommend? A. Change any DAX measures to use iterator functions. B. Enable visual interactions. C. Replace the default visuals with AppSource visuals. D. Split the visuals onto multiple pages. You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories. You import a fact table named Sales as shown in the exhibit. (Click the Exhibit tab.) The related dimension tables are imported into the model.Sales contains the data shown in the following table. You are evaluating how to optimize the model. For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point. Hot Area: YES/NO The SalesRowID and AuditID columns can be removed from the model without impeding the analysis goals Both the OrderDateKey and OrderDate columns are necessary to perform the basket