Microsoft Microsoft Certifications PL-300 Questions & Answers

• Question 231:

  DRAG DROP

  You are building a dataset from a JSON file that contains an array of documents.

  You need to import attributes as columns from all the documents in the JSON file. The solution must ensure that date attributes can be used as date hierarchies in Microsoft Power BI 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.

  Select and Place:

  

  Correct Answer:

  

  1- Convert list to table 2- Expand Column 3- Set Date type

  Here is an example: https://youtu.be/B4kzyxnhQfI

  The definition of the function which expand columns: https://docs.microsoft.com/en- us/powerquery-m/table-expandrecordcolumn

• Question 232:

  DRAG DROP

  You have a Microsoft Power BI data model that contains three tables named Sales, Product, and Date.

  The Sales table has an existing measure named [Total Sales] that sums the total sales from the Sales table.

  You need to write a calculation that returns the percentage of total sales that a selected ProductCategoryName value represents. The calculation must respect any slicers on ProductCategoryName and must show the percentage of visible

  total sales. For example, if there are four ProductCategoryName values, and a user filters one out, a table showing ProductCategoryName and the calculation must sum up to 100 percent.

  How should you complete the calculation? To answer, drag the appropriate values to the correct targets. Each value 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:

  

  Box 1: DIVIDE

  As a data modeler, when you write a DAX expression to divide a numerator by a denominator, you can choose to use the DIVIDE function or the divide operator (/ - forward slash). When using the DIVIDE function, you must pass in numerator

  and denominator expressions.

  Box 2: CALCULATE

  CALCULATE rvaluates an expression in a modified filter context.

  Box 3: ALLSELECTED

  ALLSELECTED removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

  The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

  Example:

  measure 'Reseller Sales'[Reseller Visual Total]=calculate(sum('Reseller Sales'[Sales Amount]), ALLSELECTED())

  Reference: https://docs.microsoft.com/en-us/dax/allselected-function-dax

• Question 233:

  DRAG DROP

  You are modeling data in a table named SalesDetail by using Microsoft Power BI.

  You need to provide end users with access to the summary statistics about the SalesDetail data. The users require insights on the completeness of the data and the value distributions.

  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:

  

  Step 1: Create a blank query as a data source

  Start with a New Source in Power Query Editor, and then Blank Query.

  

  Create a parameter that use a query for suggested values.

  Step 2: Specify the following query, then close and apply. -Table.Profile(#¨SalesDetail")

  In the new blank query, in the formula bar (if you don't see the formula bar, check the formula bar option in the View tab of the Power Query Editor), type below expression:

  =Table.Profile()

  Note that this code is not complete yet, we need to provide a table as the input of this function.

  Note: The Table.Profile() function takes a value of type table and returns a table that displays, for each column in the original table, the minimum, maximum, average, standard deviation, count of values, count of null values and count of

  distinct values.

  Step 3: Create a visual for the query table.

  The profiling data that you get from Table.Profile function is like below;

  

  After loading the data into Power BI, you’ll have the table with all columns, and it can be used in any visuals.

  Reference: https://radacad.com/create-a-profiling-report-in-power-bi-give-the-end-user-information-about-the-data

• Question 234:

  DRAG DROP

  You receive revenue data that must be included in Microsoft Power BI reports.

  You preview the data from a Microsoft Excel source in Power Query as shown in the following exhibit.

  

  You plan to create several visuals from the data, including a visual that shows revenue split by year and product.

  You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain.

  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:

  

  Step 1: Select Use Header as First Row.

  Step 2: Select Department and Product and Unpivot Other Columns

  Unpivot Other Columns: This command unpivots unselected columns. Use this command in a query when not all columns are known. New columns added during a refresh operation are also unpivoted.

  Step 3: Rename the Attribute column to Year and the Value column to Revenue.

  You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column. This is necessary, for example, to create a chart or a report.

  

  When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new columns and re-orient them into flattened columns:

  Values (in blue on the left) are unpivoted into a new column (in blue on the right).

  Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are correspondingly mapped to the new Values column.

• Question 235:

  DRAG DROP

  You have a Microsoft Power BI workspace.

  You need to grant the user capabilities shown in the following table.

  

  The solution must use the principle of least privilege.

  Which user role should you assign to each user? To answer, drag the appropriate roles to the correct users. Each role 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:

  

  Box 1: Member

  

  Incorrect Answers:

  Contributors can update the app metadata but not publish a new app or change who has permission to the app.

  Box 2: Contributor

  

  Reference: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-new-workspaces

• Question 236:

  HOTSPOT

  You have a Power BI report that contains a page. The page contains the following visuals:

  A card

  A matrix

  A bar chart

  You need to configure the page to ensure that the card and the bar chart are unaffected when a user drills down in the matrix The card and the bar chart must change when a user selects a cell in the matrix.

  What should you configure? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

• Question 237:

  DRAG DROP

  You build a report about warehouse inventory data. The dataset has more than 10 million product records from 200 warehouses worldwide.

  You have a table named Products that contains the columns shown in the following table.

  

  Warehouse managers report that it is difficult to use the report because the report uses only the product name in tables and visuals. The product name is contained within the ProductDescription column and is always the fourth value.

  You need to modify the report to support the warehouse managers requirement to explore inventory levels at different levels of the product hierarchy. The solution must minimize the model size.

  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:

  

  Power BI Desktop supports the use of inline hierarchy labels. With inline hierarchy labels, you can see hierarchy labels as you expand visuals using the Expand All feature.

  Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-inline-hierarchy-labels

• Question 238:

  HOTSPOT

  You have a query named All Sales that imports sales data into a Power BI model.

  You plan to create a star schema by separating columns into separate queries and performing further transformations. The solution must meet the following requirements:

  Use All Sales as the source for three other queries named Sales Fact Product Dimension, and Customer Dimension.

  Minimize maintenance effort.

  What should you do to create the Sales Fact query, and for which query should you clear Enable load? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 239:

  HOTSPOT

  You are creating a Power 81 model to analyze inventory.

  You load data into three tables named Date Product, and Inventor/. The Inventor/ table relates to the Date and Product tables by using one-to-many relationships.

  Inventor)' data is recorded daily with no exceptions. The correct inventory quantity for a given product in a month is the last recorded value for that month

  You need to write a DAX measure that will show the correct inventory value when a user analyzes inventory by year, month, or date.

  How should you complete the measure? To answer select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point

  Hot Area:

  

  Correct Answer:

  

• Question 240:

  HOTSPOT

  You create a Power Bl 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.

  Hot Area:

  

  Correct Answer: