Microsoft Microsoft Certified: Data Analyst Associate PL-300 Questions & Answers

• Question 281:

  DRAG DROP

  You have a Power BI model that contains a table named Sales. Sales has the following three measures:

  A measure named Total Sales Last Year that displays the sales from the previous calendar year. The current value is 32.89 million. A measure named Total Sales This Year that displays the sales from the current calendar year. The current value is 11.69 million. A measure named Total Sales Difference that uses a DAX formula of Sales[Last Year] ?Sales[This Year].

  You need to create the following visualization.

  

  How should you configure the visualization? To answer, drag the appropriate measures to the correct fields. 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:

  

  References: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-radial-gauge-charts

• Question 282:

  DRAG DROP

  You have the line chart shown in the exhibit. (Click the Exhibit tab.)

  

  You need to modify the chart to meet the following requirements:

  1.

  Identify months that have order counts above the mean.

  2.

  Display the mean monthly order count.

  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.

  Select the line chart

  2.

  Add the average line

  3.

  Turn on Data Label

  Reference: https://community.powerbi.com/t5/Desktop/Moving-Average/td-p/43041

• Question 283:

  DRAG DROP

  You are preparing a financial report in Power BI.

  You connect to the data stored in a Microsoft Excel spreadsheet by using Power Query Editor as shown in the following exhibit.

  

  You need to prepare the data to support the following:

  1.

  Visualizations that include all measures in the data over time

  2.

  Year-over-year calculations for all the measures

  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:

  

  Correct Answer:

  

  1.

  Use first row as header

  2.

  Unpivot all columns other than "Measure"

  3.

  Rename "Attribute" to "Year"

  4.

  Change data type of "Year" to date (Date > Year)

  Reference: https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

• Question 284:

  DRAG DROP

  You have a query named Customer that imports CSV files from a data lake. The query contains 500 rows as shown in the exhibit. (Click the Exhibit tab.)

  

  Each file contains deltas of any new or modified rows from each load to the data lake.

  Multiple files can have the same customer ID.

  You need to keep only the last modified row for each customer ID.

  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) Duplicate Customer query

  2) Group by CustId by Max ModifiedDate (only 2 columns to keep)

  3) Merge two queries on CustId and ModifiedDate inner join (to retreive other customer informations related to latest Date)

• Question 285:

  DRAG DROP

  You are using existing reports to build a dashboard that will be viewed frequently in portrait mode on mobile phones.

  You need to build the dashboard.

  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:

  

  Correct Answer:

  

  When you view dashboards in portrait mode on a phone, you notice the dashboard tiles are laid out one after another, all the same size. In the Power BI service, you can create a customized view of a dashboard, specifically for portrait mode

  on phones.

  Step 1: Open the Dashboard.

  Step 2: Change the dashboard view to Phone view

  Step 3: Pin items from the reports to the dashboard.

  Step 4: Rearrange, resize, or remote items from the Phone view.

  Create a phone view of a dashboard

  1.

  In the Power BI service, open a dashboard (Step 1)

  2.

  Select the arrow next to Web view in the upper-right corner > select Phone view. (Step2)

  

  The phone dashboard edit view opens. Here you can unpin, resize, and rearrange tiles to fit the phone view. The web version of the dashboard doesn't change.

  3.

  Select a tile to drag, resize, or unpin it. You notice the other tiles move out of the way as you drag a tile. The unpinned tiles go in the Unpinned tiles pane, where they stay unless you add them back.

  4.

  If you change your mind, select Reset tiles to put them back in the size and order they were before.

  5.

  When you're satisfied with the phone dashboard layout, select the arrow next to Phone view in the upper-right corner > select Web view.

  Power BI saves the phone layout automatically.

• Question 286:

  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 287:

  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 288:

  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 289:

  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 290:

  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.

  Reference: