Font size
WorksheetsPL-300
Total questions: 20
Worksheet time: 40mins
You import a Customer Purchases Excel file into Power BI Desktop. Below is the imported dataset in Power BI. (The Purchases column stores the data in JSON format). Which of the following operations would you use to parse the JSON data to Power BI columns?
Convert to Table
Transform/Parse to JSON
Convert to List
Expand Purchase to New Rows
You have a dataset on the bulk orders your company received from across India via different Sales Channels. You have to create a transformation query for the No of days the bulk orders are received from each City and Sales channel. Which of the following options would you choose?
Create an Advanced Group by transform. And set the Group by operation to Count Rows
Create an Advanced Group by transform. And set the Group by operation to All Rows
Create a Basic Group by transform. And set the Group by operation to Count Distinct Rows
Create an Advanced Group by transform. And set the Group by operation to Count Distinct Rows
An organization publishes employee referral reports so employees can view upcoming opportunities and refer their pals to suitable job openings. They implement row-level security so employees can view the opportunities only in their department. Employees should not be able to modify report filters to view openings in other departments.
How would you manage employees’ access to reports?
Add employees to the member role of the workspace
Distribute the referral reports as an app
Publish only the reports to the workspace, leaving out the dataset
Do not add the employee to any row-level security role
An automobile manufacturing company sources parts from suppliers in different regions of the country. The Supplier and the Purchase table have a one-to-many relationship through the Supplier Key column (shown below). You have to create a new calculated table with records only for the top 2 regions where the company orders the most no. of units. Which of the following DAX formula would you use?
Top 2 Regions = TOPN ( 2, Supplier, [Total Units], DESC )
Top 2 Regions = TOPN ( 2, Purchase, Purchase[Ordered Units], DESC )
Top 2 Regions = TOPN ( 2, Supplier[Region], [Total Units], DESC )
Top 2 Regions = TOPN ( 2, VALUES ( Supplier[ Region] ), Purchase[Total Units], DESC )
Harsh Agarwal is a famous affiliate marketer from India. Below is a sample data that displays the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.
Carefully observe the sample Affiliate Revenue dataset below. He would like to calculate the average income he earns per day from all the affiliate channels across different years.
Which two steps can help him achieve the goal?
1. Drag the Year field (from a related Date dimension) and the Affiliate Channel field into the Rows well of a table matrix visualization.
2. Drag the Affiliate Income field into the Values well and sets the default summarization to Average.
1. Drag the Year field (from a related Date dimension) and the Affiliate Channel field into the Rows well of a table matrix visualization.
2. Create an explicit measure with AVERAGEX function (for displaying average income per day).
1. Drag the Year field (from a related Date dimension) and the Affiliate Channel field into the Rows well of a table matrix visualization.
2. Create an explicit measure with AVERAGEA function (for displaying average income per day).
In addition to telling stories in a Power BI report, there is a requirement to display table statistics and data distribution profiles of columns in a fact table for end users. Which of the following tools/scenarios is a feasible/best solution you would employ?
Note: Assume the fact table has a large number of columns.
Use the Table.Profile function on the fact table in Power Query
In Power Query, use the column profile tool
In Power Query, use the column quality tool
Create a table visual in Power BI using aggregate functions
You have a dataset on the bulk orders your company received from across India via different Sales Channels. You have to create a transformation query for the No of days the bulk orders are received from each City and Sales channel. Which of the following options would you choose?
Create an Advanced Group by transform. And set the Group by operation to Count Rows
Create an Advanced Group by transform. And set the Group by operation to All Rows
Create a Basic Group by transform. And set the Group by operation to Count Distinct Rows
Create an Advanced Group by transform. And set the Group by operation to Count Distinct Rows
You have a project management application that is fully hosted on Microsoft Teams. This application was developed using Microsoft Power Apps.
You need to create a Power BI report connected to a project management application.
Which connector should you choose?
Microsoft Teams Personal Analytics
SQL Server database
Dataverse
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?
Power BI Dataset
SharePoint Folder
Power BI Dataflow
Excel
A query operation that joins two existing tables based on matching values from one or more columns.
Join
Merge
Append
Group
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?
Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
Change the data type of the Logged column to Date.
Split the Logged column by using at as the delimiter.
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?
Excel Workbook
CSV
Folder
Sharepoint Folder
Web
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?
Add a SQL statement.
Set the Command timeout in minutes setting.
Set Data Connectivity mode to Import.
Set Data Connectivity mode to DirectQuery.
What type of expression do you use to extract data from Microsoft SQL Server?
DAX
T-SQL
MDX
What is a risk of having null values in a numeric column?
That function SUM of data will be incorrect.
That function MAX of data will be incorrect.
That function AVERAGE of data will be incorrect.
If you have two queries that have different data but the same column headers, and you want to combine both tables into one query with all the combined rows, which operation should you perform?
Append
Merge
Join
Combine
What is the difference between a fact table and a dimension table?
Fact tables contain observational data while dimension tables contain information about specific entities within the data.
Fact tables contain information about specific entities while dimension tables contain information about observational data.
There is no difference.
Dimension tables tell you about specific roles in Power BI while fact tables tell you information about facts that are associated with those roles in Power BI.
If you want to create a measure that always calculates the total sales for 2015, regardless of which year is selected in any other visual in Power BI, you would create a measure
Total Sales for 2015 = SUM(CALCULATE('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015)
Total Sales for 2015 = CALCULATE(SUM('Sales OrderDetails'[Total Price]), DATE('Sales OrderDetails'[orderdate]) = 2015)
Total Sales for 2015 = CALCULATE(SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015)
Total Sales for 2015 = CALCULATE(SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[Date]) = 2015)
Which two functions will help you compare dates to the previous month?
CALCULATE and PREVIOUSMONTH
TOTALYTD and PREVIOUS MONTH
CALCULATE and TOTALTYD
Is it possible to create a relationship between two columns if they are different DATA TYPE columns?
Yes, if cardinality of the relationship is set to Many-to-Many.
No, both columns in a relationship must be sharing the same DATA TYPE.
Yes, the above is fully supported in latest version of Power BI desktop.
