Question 1mediummultiple choice
Read the full Prepare the data explanation →PL-300 Prepare the data • Complete Question Bank
Complete PL-300 Prepare the data question bank — all 0 questions with answers and detailed explanations.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag a concept onto its matching description — or click a concept then click the description.
Modifies the filter context
Evaluates an expression for each row and sums the results
Returns a table that represents a subset of another table
Clears all filters from a table or column
Returns a related value from another table
Drag a concept onto its matching description — or click a concept then click the description.
Show trends over time
Show proportions of a whole
Show relationship between two variables
Compare parts of a category
Show stages in a process
Drag a concept onto its matching description — or click a concept then click the description.
Fact and dimension tables in a denormalized structure
Uniqueness of values in a column
Link between tables based on common columns
Dynamic calculation using DAX
Static column added to a table using DAX
Refer to the exhibit.
{
"dataSources": [
{
"name": "SalesData",
"connectionDetails": {
"server": "myserver.database.windows.net",
"database": "SalesDB",
"authenticationKind": "UsernamePassword",
"encryptedConnection": "Encrypted",
"privacyLevel": "Private"
}
}
]
}Refer to the exhibit.
let
Source = Sql.Database("myserver.database.windows.net", "SalesDB"),
SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2023,1,1)),
RemovedColumns = Table.RemoveColumns(FilteredRows,{"Discount"}),
GroupedRows = Table.Group(RemovedColumns, {"CustomerID"}, {{"Total", each List.Sum([Amount]), type number}})
in
GroupedRowsRefer to the exhibit.
EVALUATE
SUMMARIZE(
'Sales',
'Product'[Category],
"Total Sales", SUM('Sales'[Amount])
)Refer to the exhibit. ```kql // KQL query in Power Query let StartDate = datetime(2023-01-01); let EndDate = datetime(2023-12-31); TableName | where Timestamp between (StartDate .. EndDate) | project-away InternalField | summarize TotalSales = sum(SalesAmount) by Region ```
Refer to the exhibit.
```json
{
"dataSources": [
{
"name": "SalesDB",
"connectionString": "Data Source=sqlserver.contoso.com;Initial Catalog=SalesDB;Integrated Security=SSPI;"
}
],
"parameters": [
{
"name": "StartDate",
"currentValue": "2023-01-01"
}
]
}
```Refer to the exhibit.
```json
[
{
"name": "Sales",
"mode": "Import",
"source": {
"type": "Sql",
"query": "SELECT * FROM Sales WHERE Year = @Year"
},
"parameters": [
{
"name": "Year",
"required": true,
"type": "Int64.Type"
}
]
}
]
```Refer to the exhibit.
```json
{
"dataSources": [
{
"connectionDetails": {
"server": "myserver.database.windows.net",
"database": "SalesDB",
"authenticationKind": "Key",
"options": {}
},
"datasourceType": "SQL"
}
],
"pbiServiceConfiguration": {
"useSingleSignOn": false,
"gatewayClusterId": "12345",
"gatewayClusterName": "OnPremGateway"
}
}
```Refer to the exhibit.
```dax
EVALUATE
SUMMARIZECOLUMNS(
'Sales'[ProductID],
'Sales'[Region],
FILTER('Sales', 'Sales'[Quantity] > 10),
"TotalSales", SUM('Sales'[Amount])
)
```Refer to the exhibit.
let
Source = Sql.Database("server01", "AdventureWorks"),
dbo_SalesOrderHeader = Source{[Schema="dbo",Item="SalesOrderHeader"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_SalesOrderHeader, each Date.Year([OrderDate]) = 2024),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerID"}, {{"TotalSales", each List.Sum([SubTotal]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{["TotalSales"], Order.Descending}})
in
#"Sorted Rows"Refer to the exhibit.
{
"dataSources": [
{
"name": "SalesSQL",
"connectionString": "Data Source=sqlserver01;Initial Catalog=AdventureWorks;Integrated Security=SSPI;"
},
{
"name": "MarketingCSV",
"connectionString": "C:\\Data\\Marketing.csv"
}
],
"privacyLevels": {
"SalesSQL": "Organizational",
"MarketingCSV": "Private"
}
}Refer to the exhibit.
let
Source = Csv.Document(File.Contents("C:\Data\Sales.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Amount", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"Product"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Amount] > 0)
in
#"Filtered Rows"Refer to the exhibit.
```json
{
"dataSources": [
{
"name": "SalesDB",
"connectionDetails": {
"server": "sqlsrv-prod.database.windows.net",
"database": "SalesDB",
"authenticationKind": "Key",
"options": {
"CommandTimeout": 600,
"CreateNavigationProperties": false
}
}
}
]
}
```Refer to the exhibit.
```
Table.TransformColumnTypes(Source,{{"SalesAmount", type number},
{"OrderDate", type datetime},
{"CustomerID", type text}})
```Refer to the exhibit.
```
let
Source = Csv.Document(File.Contents("C:\data\sales.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type datetime}, {"Amount", type number}})
in
#"Changed Type"
```Refer to the exhibit.
```
let
Source = Sql.Database("server.database.windows.net", "AdventureWorks", [Query="SELECT * FROM Sales.SalesOrderDetail WHERE ModifiedDate > '2024-01-01'"]),
#"Filtered Rows" = Table.SelectRows(Source, each [OrderQty] > 10),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"ProductID"}, {{"TotalQty", each List.Sum([OrderQty]), type number}})
in
#"Grouped Rows"
```Refer to the exhibit.
{
"dataSources": [
{
"name": "SalesDB",
"connectionString": "Data Source=salesdb.contoso.com;Initial Catalog=Sales;Integrated Security=SSPI;"
}
],
"dataset": {
"tables": [
{
"name": "Sales",
"source": {
"type": "m",
"expression": "let
Source = Sql.Database(\"salesdb.contoso.com\", \"Sales\"),
dbo_Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data],
#\"Filtered Rows\" = Table.SelectRows(dbo_Sales, each [OrderDate] >= #date(2020,1,1) and [OrderDate] <= #date(2024,12,31))
in
#\"Filtered Rows\"
"
},
"columns": [
{"name": "OrderID", "dataType": "Int64"},
{"name": "OrderDate", "dataType": "DateTime"},
{"name": "Amount", "dataType": "Decimal"}
]
}
]
}
}Refer to the exhibit. Output of 'Get-AzPowerBIDataset -WorkspaceId "12345678-1234-1234-1234-123456789012"' Name Id AddRowsAPIEnabled IsRefreshable TargetStorageMode ---- -- ------------------ ------------- ------------------ Sales Analysis 87654321-4321-4321-4321-210987654321 False True Abc Profit Report 87654321-4321-4321-4321-210987654322 True False Csv Inventory Dataset 87654321-4321-4321-4321-210987654323 False False Abc
Refer to the exhibit.
{
"dataSources": [
{
"name": "SalesData",
"connectionString": "Data Source=server.contoso.com;Initial Catalog=SalesDB;Integrated Security=SSPI;"
}
],
"dataset": {
"tables": [
{
"name": "Orders",
"source": {
"type": "m",
"expression": "let
Source = Sql.Database(\"server.contoso.com\", \"SalesDB\"),
dbo_Orders = Source{[Schema=\"dbo\",Item=\"Orders\"]}[Data],
#\"Filtered Rows\" = Table.SelectRows(dbo_Orders, each [OrderDate] > #date(2025,1,1))
in
#\"Filtered Rows\"
"
},
"columns": [
{"name": "OrderID", "dataType": "Int64"},
{"name": "OrderDate", "dataType": "DateTime"},
{"name": "Amount", "dataType": "Decimal"}
]
}
]
}
}{
"jobType": "FullRefresh",
"mode": "IncrementalRefresh",
"pollingInterval": 30,
"sourceType": "Sql",
"parallelism": 5,
"maxParallelism": 10,
"retryCount": 3
}let
Source = Sql.Database("server", "database"),
Sales = Source{[Schema="dbo"][Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(Sales, each [Year] >= 2020),
GroupedRows = Table.Group(FilteredRows, {"Region"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
GroupedRowsRefer to the exhibit.
// DAX Measure in a Power BI dataset
Total Sales =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
// Another measure
Sales LY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
)
// Then a third measure
Sales YoY % =
DIVIDE(
[Total Sales] - [Sales LY],
[Sales LY]
)Refer to the exhibit.
// Power Query M code
let
Source = Sql.Database("server", "database"),
dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_Orders, each [OrderDate] > #date(2024,1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CreditCardNumber"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"OrderDate", type date}})
in
#"Changed Type"Refer to the exhibit.
// JSON policy for Power BI data source
{
"version": "1.0",
"dataSource": {
"type": "Sql",
"connectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydb;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False"
},
"credentials": {
"authenticationKind": "ServicePrincipal",
"servicePrincipalId": "abc-123",
"tenantId": "tenant-id"
}
}Refer to the exhibit.
// M query snippet
let
Source = Excel.Workbook(File.Contents("C:\Sales.xlsx"), null, true),
Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"Date", type date}, {"Amount", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(2024,1,1))
in
#"Filtered Rows"Refer to the exhibit.
// Power Query M code snippet
let
Source = Sql.Database("myserver.database.windows.net", "SalesDB"),
dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_Orders, each [OrderDate] > #date(2024,6,1))
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"OrderDetails"})
in
#"Removed Columns"Refer to the exhibit.
// DAX calculated table definition
DateTable =
VAR StartDate = DATE(2020,1,1)
VAR EndDate = DATE(2025,12,31)
RETURN
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMMM"),
"MonthNumber", MONTH([Date])
)Refer to the exhibit.
```json
{
"dataSources": [
{
"name": "SalesDB",
"connectionString": "Data Source=sqlserver01;Initial Catalog=Sales;Integrated Security=SSPI;"
}
],
"permissions": [
{
"user": "analyst@contoso.com",
"dataSources": ["SalesDB"],
"tables": ["Orders"]
}
]
}
```Refer to the exhibit.
```dax
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Date] >= DATE(2024,1,1) && Sales[Date] <= DATE(2024,12,31)
)
)
```Refer to the exhibit.
```json
{
"refreshSchedule": {
"frequency": "Daily",
"time": "05:00",
"timeZone": "UTC",
"notifyOption": "OnFailure",
"notifyEmail": "admin@contoso.com"
}
}
```{
"dataSources": [
{
"name": "SalesDB",
"connectionString": "Data Source=sqlserver01;Initial Catalog=SalesDB;Integrated Security=SSPI",
"credentialType": "Windows",
"privacyLevel": "Organizational"
},
{
"name": "MarketingCSV",
"connectionString": "C:\\Data\\Marketing.csv",
"credentialType": "None",
"privacyLevel": "Private"
}
]
}let
Source = Sql.Database("sqlserver01", "SalesDB"),
dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_Sales, each [Year] >= 2020)
in
#"Filtered Rows"{
"credentials": [
{
"datasource": "AzureBlob",
"credentialType": "Anonymous"
},
{
"datasource": "SqlServer",
"credentialType": "Basic",
"username": "bi_user",
"password": "EncryptedPassword"
}
]
}Refer to the exhibit.
```json
{
"workspaceId": "12345678-1234-1234-1234-123456789012",
"dataflowId": "87654321-4321-4321-4321-210987654321",
"dataflowName": "SalesDataflow",
"entities": [
{
"name": "Sales",
"source": "AzureSqlDatabase",
"refreshPolicy": {
"type": "Full",
"interval": "Daily"
}
},
{
"name": "Product",
"source": "SharePointList",
"refreshPolicy": {
"type": "IncrementalRefresh",
"interval": "Hourly",
"incrementalGranularity": "Day"
}
}
]
}
```Refer to the exhibit.
```json
{
"name": "SalesDataConnector",
"type": "SQL",
"connectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=SalesDB;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;",
"authentication": {
"method": "OAuth2",
"clientId": "00000000-0000-0000-0000-000000000000",
"authority": "https://login.microsoftonline.com/common",
"resource": "https://database.windows.net/"
}
}
```Refer to the exhibit.
```
M query:
let
Source = Sql.Database("myserver.database.windows.net", "SalesDB"),
SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2023,1,1)),
GroupedRows = Table.Group(FilteredRows, {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
GroupedRows
```Refer to the exhibit. The following Power Query M code is used to combine multiple CSV files from a folder:
let
Source = Folder.Files("C:\Data\Sales"),
Filtered = Table.SelectRows(Source, each [Extension] = ".csv"),
Combined = Table.Combine(Table.TransformColumns(Filtered, {{"Content", each Csv.Document(_)}}))
in
CombinedRefer to the exhibit. The following Power Query M function is defined:
(value as any) as nullable number =>
try Number.From(Text.Replace(Text.Clean(value), " ", ""))
otherwise nullRefer to the exhibit. The following Power BI DAX expression is used to create a calculated column: Sales Rank = RANKX(ALL(Sales), SUM(Sales[Amount]),, DESC, Dense)
You are a data analyst for a global retail company. The company uses Power BI Premium capacity. You are building a dataset that combines sales data from three sources: 1. An Azure SQL Database that stores transactional sales data (10 million rows per day, retained for 5 years). 2. A SharePoint Online folder containing monthly Excel reports from regional offices (each report has a different structure). 3. A Dataverse table that contains customer feedback scores.
Requirements: - The dataset must support near real-time reporting for the current month's sales (maximum 15-minute latency). - Historical sales data (older than current month) can be refreshed daily. - Customer feedback scores should be updated every hour. - The Excel reports from SharePoint must be combined into a single table with consistent columns. - The final dataset should be optimized for fast query performance.
You need to design the data preparation strategy. What should you do?
You are a Power BI developer for a healthcare organization. You are building a dataset that includes patient data from an on-premises SQL Server database. The database contains a table 'PatientVisits' with columns: PatientID, VisitDate, DiagnosisCode, and Cost. The database also has a table 'DiagnosisLookup' with DiagnosisCode and Description. You need to create a star schema in Power BI. The requirements are: - The dataset must include a date dimension table that covers all dates from 2010 to 2030. - The 'PatientVisits' table should be the fact table. - Diagnosis descriptions should be in a dimension table. - You must use Power Query to create the date dimension table using M code. - The data refresh must be scheduled daily via the on-premises data gateway.
You have already loaded the 'PatientVisits' and 'DiagnosisLookup' tables. What should you do next to complete the star schema?
You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are: 1. Combine all CSV files from a folder into a single table. 2. Replace null values in ScrapUnits with 0. 3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence. 4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number).
Which sequence of Power Query steps should you use?