📘 Free PL-300 Sample Questions
HOTSPOT -
You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.)
A
Correct Answer:
A.
Explanation:
Box 1: Dual -
Customer should use the dual storage mode.
Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's submitted to the Power BI dataset. In some cases, you fulfill queries from cached data. In other cases, you fulfill queries by executing an on-demand query to the data source.
Note: You set the Storage mode property to one of these three values: Import, DirectQuery, and Dual. Box 2: Dual -
You can set the dimension tables (Customer, Geography, and Date) to Dual to reduce the number of limited relationships in the dataset, and improve performance.
Box 3: DirectQuery -
Sales should use the DirectQuery storage mode.
DirectQuery: Tables with this setting aren't cached. Queries that you submit to the Power BI dataset"for example, DAX queries"and that return data from
DirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you submit to the data source use the query language for that data source, for example, SQL.
Box 4: Import -
Import: Imported tables with this setting are cached. Queries submitted to the Power BI dataset that return data from Import tables can be fulfilled only from cached data.
Note:-
Dual (Composite) Mode:
The dual storage mode is between Import and DirectQuery. it is a hybrid approach, Like importing data, the dual storage mode caches the data in the table. However, it leaves it up to Power BI to determine the best way to query the table depending on the query context.
1) Sales Must be Refreshed in Near real time so "Direct Query"
2) Sales Aggregate is once per week so "Import" (performance also required)
3) Both Date and Customer has relationship with both Sales and SalesAggregate tables so "Dual" because to support performance for DirectQuery(Sales) and Import(SalesAggregate)
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode
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. Dataverse
Explanation:
Data sources in Power BI Desktop.
The Power Platform category provides the following data connections: Power BI datasets -
Power BI dataflows -
Common Data Service (Legacy) Dataverse -
Dataflows -
Other data sources include Microsoft Teams Personal Analytics (Beta).
You can use the Microsoft Power BI template to import data into Power BI from Project for the web and Project Online. When you're using the template, you're connected to your Microsoft Dataverse instance, where your Microsoft Project web app data is stored.
https://support.microsoft.com/en-us/office/use-power-bi-desktop-to-connect-with-your-project-data- df4ccca1-68e9-418c-9d0f-022ac05249a2
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources
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. Power BI dataset
Explanation:
Power BI dataset
because the case states there is already a report published and the datamodel contains measures. therefore and to be able to use the measures in the datamodel you should connect to the existing dataset (which was created when you plublished the report) instead of starting from scratch with the files in the SharePoint folder.
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. Merge the Customer and Address tables.
Explanation:
Remember Merge is JOIN, APPEND is UNION
A merge queries operation joins two existing tables together based on matching values from one or multiple columns. You can choose to use different types of joins, depending on the output you want.
Reference:
https://docs.microsoft.com/en-us/power-query/merge-queries-overview
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:
A
Correct Answer:
A.
Explanation:
Box 1: Append Queries as New -
When you have additional rows of data that you'd like to add to an existing query, you append the query. There are two append options:
* Append queries as new displays the Append dialog box to create a new query by appending multiple tables.
* Append queries displays the Append dialog box to add additional tables to the current query.
Incorrect: When you have one or more columns that you'd like to add to another query, you merge the queries. Box 2: Disable loading the query to the data model
By default, all queries from Query Editor will be loaded into the memory of Power BI Model. You can disable the load for some queries, especially queries that used as intermediate transformation to produce the final query for the model.
Disabling Load doesn't mean the query won't be refreshed, it only means the query won't be loaded into the memory. When you click on Refresh model in Power
BI, or when a scheduled refresh happens even queries marked as Disable Load will be refreshed, but their data will be used as intermediate source for other queries instead of loading directly into the model. This is a very basic performance tuning tip, but very important when your Power BI model grows bigger and bigger.
Reference:
https://docs.microsoft.com/en-us/power-query/append-queries
https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up
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:
A
Correct Answer:
A.
Explanation:
Box 1: Inner -
Every Product has a ProductSubCategory. A standard join is needed.
One of the join kinds available in the Merge dialog box in Power Query is an inner join, which brings in only matching rows from both the left and right tables.
Box 2: Left outer -
Not every ProductsubCategory has a parent ProductCategory.
One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.
Reference:
https://docs.microsoft.com/en-us/power-query/merge-queries-inner
https://docs.microsoft.com/en-us/power- query/merge-queries-left-outer
You are building a Power BI report that uses data from an Azure SQL database named erp1. You import the following tables.
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. From Power Query, merge the Orders query and the Order Line Items query.
Explanation:
D. It's the Header/Detail Schema, and the most optimal way is to flatten the header into the detail table.
Source:
https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/
GPT: Merging the Orders query and the Order Line Items query in Power Query will allow you to create a single query that combines the necessary data from the different tables. This will make it easier and more efficient to perform the required analyses, as you will have all the information you need in one place.
--- PBI will do the best aggregation base on Star Schema model, we now have 1 Fact table (Order Line Items) and 2 Dim tables (Products, Orders). Orders has common field with Products (ProductID), and pretty sure time
series field (OrderDate); Orders Line Items has Price and Quanity.
--- We need summarize some values like "price" and "quantity" over-time by attributes product. But we only have common field in Dim table (Orders) so we need to merge Dim (Orders) and Fact (Order Line Items) to new single Fact table to design the right Star Schema model.
=> So that D is correct
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. Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.
Explanation:
We have to import Excel files from SharePoint, so we need the connector SharePoint folder which is used to get access to the files stored in the library. SharePoint list is a collection of content that has rows and columns (like a table) and is used for task lists, calendars, etc.
Since we have to filter only on manufacturing reports, we have to select Transform and then filter by the corresponding folder path.
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. Split the Logged column by using at as the delimiter.
Explanation:
You should split the Logged column by using "at" as the delimiter. This will allow you to separate the date and time into separate columns, which will enable you to analyze the complaints by date and use a built-in date hierarchy. Alternatively, you could also use a transformation to extract the date and time from the Logged column and set the data type of the new columns to Date and Time, respectively. Option A is incorrect because it only extracts the last 11 characters of the Logged column, which would not include the date. Option B is incorrect because the data in the Logged column is in a non-standard date format and cannot be directly converted to the Date data type. Option D is incorrect because it only extracts the first 11 characters of the Logged column, which would not include the time.
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
Correct Answer:
D. SharePoint folder
Explanation:
We can import an excel file from multiple connectors (excel workbook, folder, web, share point) but if we must refresh the data from the service with no gateways then We must use web and share point connectors.
Questions: 1-10 out of 401
Continue Full Practice..
GET ALL 401 QUESTIONS