PL-300 Prepare the data Practice Test 8 — 15 Questions

Question 1

You are a data analyst for a retail company. You receive daily sales data in CSV files stored in an Azure Blob Storage container. The files have a consistent naming convention: Sales_YYYYMMDD.csv. You need to load only the latest file into Power BI each day, using Power Query. You want to automate this process so that each time you refresh the dataset, it picks up the most recent file. You have already created a connection to the container. What should you do in Power Query to ensure only the latest file is loaded?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use 'From Folder' connector, filter by creation date to keep only the latest file.. The 'From Folder' connector in Power Query allows you to connect to a folder (including Azure Blob Storage containers mapped as folders) and then filter files by creation date or last modified date. By filtering to keep only the latest file, you ensure that each refresh loads the most recent CSV without manual intervention. This approach fully automates the process, as Power Query will evaluate the filter each time the query refreshes.

Answer

Use 'From Azure Blob Storage' and filter by last modified date.

Answer

Use 'Combine Files' and then filter to the latest date.

Answer

Create a parameter for the file name and update it manually.

Question 2

You are preparing a Power BI dataset that uses a SQL Server database as the source. Your manager asks you to ensure that the dataset refreshes automatically every morning at 6:00 AM. You have configured a scheduled refresh in the Power BI service. However, after the first scheduled refresh, you notice that it failed with an error: 'The data source credentials are invalid.' What is the most likely cause and solution?

Accepted Answer

The data source credentials are not configured in the gateway.. The error 'The data source credentials are invalid' indicates that the credentials stored in the Power BI service for the data source are incorrect or missing. Since the dataset uses a SQL Server database, which is an on-premises data source, the credentials must be configured within the on-premises data gateway settings in the Power BI service. Option B correctly identifies that the gateway credentials were not set up, causing the refresh to fail despite the scheduled refresh being configured.

Answer

The scheduled refresh time is not set correctly.

Answer

An on-premises data gateway is not installed.

Answer

The dataset is too large for scheduled refresh.

Question 3

You are a Power BI report creator. You have a dataset that includes a date table and a sales table. The date table has a column 'FiscalYear' (e.g., 'FY2025'). The sales table has a column 'OrderDate'. You need to create a relationship between the date table and the sales table based on the fiscal year. However, the date table has one row per fiscal year, and the sales table has multiple rows per fiscal year. You want to filter sales by fiscal year. What is the correct approach to model this?

Accepted Answer

Create a one-to-many relationship from Date[FiscalYear] to Sales[FiscalYear] (assuming Sales table has FiscalYear).. It establishes a standard one-to-many relationship between the Date table (with one row per fiscal year) and the Sales table (with multiple rows per fiscal year) using a common FiscalYear column. This allows Power BI to filter sales by fiscal year through the relationship, leveraging the date table as a filter dimension. The relationship must be based on a column in the Sales table that contains the fiscal year for each order, which can be added as a calculated column if not already present.

Answer

Add a calculated column in Sales to extract fiscal year from OrderDate and use that for filtering without a relationship.

Answer

Merge the Date table into the Sales table in Power Query.

Answer

Create a many-to-many relationship between Date and Sales using a bridge table.

Question 4

You are a Power BI administrator at a large enterprise. You need to ensure that all Power BI datasets in your organization use certified data sources only, to maintain data quality. You have enabled data source certification in your Microsoft Purview governance portal. Which additional step must you take to enforce that only certified data sources are used in Power BI datasets?

Accepted Answer

In the Power BI admin portal, enable the 'Require certified data sources' tenant setting.. The 'Require certified data sources' tenant setting in the Power BI admin portal is the specific control that enforces datasets to use only data sources that have been certified in Microsoft Purview. Without this setting enabled, certification is merely informational and not enforced, allowing uncertified sources to be used. This setting works in conjunction with Purview's data source certification to block dataset creation or refresh if any source lacks certification.

Answer

Create dataflows that use only certified data sources and require report creators to use those dataflows.

Answer

Apply sensitivity labels to all certified data sources in Purview.

Answer

Configure workspace security to restrict dataset creation to administrators only.

Question 5

You are a data analyst for a healthcare organization. You have a Power BI dataset that imports patient data from an on-premises SQL Server database. The database contains personally identifiable information (PII). You need to ensure that all PII columns are obfuscated when the data is loaded into Power BI. You have already masked the columns in the source database using dynamic data masking. However, when you refresh the dataset in Power BI, the masked values are not appearing. What should you do?

Accepted Answer

In the data source settings, use a SQL Server account that has been granted the UNMASK permission to see masked data, but actually you need an account WITHOUT UNMASK to see masked data. Actually, correct: Configure the data source connection to use a user without the UNMASK permission so that dynamic data masking applies.. Dynamic data masking (DDM) in SQL Server only applies to users lacking the UNMASK permission. To have Power BI load masked values, the data source connection must use an account without UNMASK. The option should state: 'Configure the data source connection to use a SQL Server account without the UNMASK permission so that dynamic data masking applies.'

Answer

In Power BI Desktop, define row-level security roles to restrict PII columns.

Answer

Apply data masking in Power Query using Text.Start or Text.End functions.

Answer

Install the on-premises data gateway and configure it to use the service account.

Question 6

You are a data analyst for an e-commerce company. You have a Power BI dataset that imports data from a Salesforce source using the Salesforce connector. The dataset is refreshed daily. Recently, the refresh started failing with the error 'Unable to connect to the data source'. You have verified that the Salesforce credentials are correct and that the Salesforce service is healthy. What is the most likely cause?

Accepted Answer

The OAuth redirect URI for the Salesforce app is not configured correctly.. The Salesforce connector in Power BI uses OAuth 2.0 for authentication. Even if credentials are correct and the Salesforce service is healthy, a misconfigured OAuth redirect URI in the Salesforce connected app prevents Power BI from completing the authorization handshake, causing the 'Unable to connect to the data source' error during refresh. This is a common issue when the redirect URI does not match the Power BI service's callback endpoint.

Answer

The on-premises data gateway is not installed.

Answer

The privacy levels for the data source are set to 'Private'.

Answer

The Power BI service is not available in the same region as Salesforce.

Question 7

You are a data analyst at an online retailer. You connect Power BI Desktop to a PostgreSQL database. The Orders table contains a column named order_date with the data type timestamp without time zone. You need to create a new column that returns only the date portion (year, month, day) and discards the time, so it can be used as a relationship key to a Date table. You want the transformation to be applied in Power Query and to not require any custom formula. Which Power Query transformation should you use?

Accepted Answer

Select the order_date column, then on the Transform tab choose Date and select Date Only.. The Date Only transformation in Power Query is the built-in operation that strips the time component from a datetime column and returns a true Date value. This matches the requirement of a no-code transformation that produces a column usable as a relationship key to a Date table. Other date transformations extract individual parts or change the type to an unsuitable format.

Answer

Select the order_date column, then on the Add Column tab choose Duration and select Total Days.

Answer

Select the order_date column and change its data type to Text.

Answer

Select the order_date column, then on the Add Column tab choose Date and select Year.

Question 8

You are a data analyst at a hospital. You connect Power BI Desktop to an Azure SQL Database and import a table named Admissions. The table has a column named AdmissionDate that is currently of data type Date/Time. You need to change the data type of this column to Date so that it matches the Date table. You want to make this change in Power Query without writing any code. What should you do?

Accepted Answer

In Power Query, select the AdmissionDate column, open the data type list, and select Date.. The correct approach is to change the data type of the AdmissionDate column to Date directly in Power Query. This updates the column in place, requires no code, and aligns the column with the Date table so a relationship can be created. Other options either create a new column, use a transformation that does not explicitly set the type, or attempt an unsupported action in the model view.

Answer

In Power Query, add a custom column using the formula Date.From([AdmissionDate]).

Answer

In the Power BI model view, select the AdmissionDate column and change its data type to Date.

Answer

In Power Query, select the AdmissionDate column and apply the Date Only transformation from the Transform tab.

Question 9

You are a Power BI data analyst at a retail chain. You connect to an Azure SQL Database and import a Sales table. The table contains a column named StoreCode that mixes two formats: some rows use a two-letter state prefix followed by a hyphen and a numeric store number (for example, 'CA-1042'), while other rows use only the numeric store number (for example, '1042'). You need to extract the numeric store number into a new column for joining to a Store dimension. You want to use Power Query transformations rather than handwritten M code. Which two actions should you perform? (Choose two.)

Accepted Answer

Change the data type of the new column to Whole Number after extracting the numeric characters.. Extracting text after the hyphen isolates the numeric portion for hyphenated rows and leaves non-hyphenated rows unchanged, covering both formats in one step. Converting the extracted text to Whole Number then aligns its type with the dimension key so the join succeeds. The other transformations either fail on one of the formats or require extra steps.

Answer

Use the Split Column > By Delimiter transformation with the hyphen as the delimiter and split at the left-most delimiter.

Answer

Use the Extract > Text Between Delimiters transformation with the hyphen as both the start and end delimiter.

Answer

Use the Replace Values transformation to replace all hyphens with empty text, then change the type to Whole Number.

Question 10

You are a Power BI data analyst at a bank. You import a table named Transactions from a SQL Server database. The table has a column named Amount stored as a decimal number and a column named TransactionDate stored as a date. You need to create a new column that contains the cumulative running total of Amount ordered by TransactionDate. The running total must reset for each AccountID. You want to implement this in Power Query without writing any custom M code. Which approach should you use?

Accepted Answer

Sort the table by AccountID and TransactionDate, then use the Add Column tab and select Index Column, and then use the Running Total transformation from the Add Column tab.. The Running Total transformation in Power Query, combined with sorting by AccountID and TransactionDate, produces a cumulative sum of Amount that resets per account. It is a built-in operation that requires no custom M code. The other options either require custom formulas, do not compute a running total, or use grouping that does not provide the desired result.

Answer

Use the Group By transformation to group by AccountID, then apply the All Rows operation, and finally expand the rows to calculate the running total.

Answer

Sort the table by AccountID and TransactionDate, then use the Add Column tab and select Index Column, and then use the Table.AddIndexColumn function with a custom formula.

Answer

Sort the table by AccountID and TransactionDate, then use the Add Index Column transformation, and finally use a custom column to calculate the running total.

Question 11

You are a Power BI data analyst at a retail company. You have a Power Query query named 'Sales' that loads data from an Azure SQL Database. The query currently has a step that filters the 'OrderDate' column to include only dates from the current year. You need to modify the query so that the filter always uses the current year dynamically, without manual updates. What should you do?

Accepted Answer

Replace the hardcoded year in the filter step with the expression Date.Year(DateTime.LocalNow()).. The goal is to dynamically filter the Sales query to include only rows where the OrderDate year equals the current year, without manual updates. Using Date.Year(DateTime.LocalNow()) in the filter step achieves this because it computes the current year at query execution time. Other methods either require manual updates, do not guarantee the current year, or filter after loading, which is not ideal for performance.

Answer

Add a parameter named 'CurrentYear' with type Date and set its current value to the current year, then reference it in the filter step.

Answer

Create a calculated column in the Sales table using DAX that flags rows where the year matches the current year, then filter the table based on that column.

Answer

Use the Table.SelectRows function with a condition that compares the year of OrderDate to the maximum year in the OrderDate column.

Question 12

You are a Power BI data analyst for a retail company. You import a table named Sales from an on-premises SQL Server. The table has a column OrderID with values like 'ORD-1001'. A new requirement states that OrderID must be stored as a whole number for use in a relationship with another table. You need to transform OrderID in Power Query without changing the source. What should you do?

Accepted Answer

Use the Replace Values transformation to remove 'ORD-' from the column, then change the data type to Whole Number.. The correct approach removes the non-numeric prefix with Replace Values and then converts the column to Whole Number. This keeps the transformation in Power Query, does not alter the SQL Server source, and produces a numeric column suitable for relationships. Other methods either fail, add unnecessary complexity, or perform the work outside the data preparation layer.

Answer

Create a calculated column in the data model using DAX: OrderIDNumber = VALUE(SUBSTITUTE(Sales[OrderID], "ORD-", "")).

Answer

In the Power Query Editor, right-click the column and select Change Type > Whole Number.

Answer

Use the Split Column transformation by delimiter '-' and keep the second part, then change the type to Whole Number.

Question 13

You are building a Power BI dataset from an on-premises SQL Server database. The FactSales table has 40 million rows, and you only need records from the last three years for your report. You want the refresh to complete as quickly as possible and minimize the data transferred to the Power BI model. You connect to the database using Power Query. What should you do?

Accepted Answer

After loading all rows into Power Query, add a filter step on the OrderDate column using the Table.SelectRows transformation.. Filtering early in Power Query with Table.SelectRows on the date column folds to a SQL WHERE clause, so SQL Server returns only the relevant rows. This shrinks the dataset before it reaches the model, cutting refresh time and memory. Report filters, calculated tables, and DirectQuery do not achieve the same early data reduction during import.

Answer

Use DirectQuery against FactSales and rely on the visual-level filter to restrict OrderDate at query time.

Answer

Import the full FactSales table and create a calculated table using DAX that filters OrderDate to the last three years.

Answer

Load all 40 million rows into the model, then create a Power BI report-level filter on the OrderDate field to display only three years.

Question 14

You are a data analyst at a retail company. You need to import a large fact table named 'Sales' from an Azure SQL Database into Power BI Desktop. The table contains over 50 million rows and includes a column 'SalesAmount' of type decimal. You want to minimize the data loaded into the Power BI model while still allowing users to analyze sales by product and date. You also need to ensure that the 'SalesAmount' column is aggregated correctly. What should you do?

Accepted Answer

Use Power Query to group the data by product and date, and aggregate the 'SalesAmount' column before loading.. Grouping and aggregating in Power Query reduces the data volume before it reaches the Power BI model, which is essential for large fact tables. This approach ensures that only summarized data is loaded, improving performance and reducing memory usage. The other options either load all rows or do not reduce the data size at import time, failing to meet the requirement.

Answer

Import the table and then create a measure that sums 'SalesAmount'.

Answer

Import the entire 'Sales' table and then create a summarized table using DAX.

Answer

Use DirectQuery to connect to the Azure SQL Database and create a measure for 'SalesAmount'.

Question 15

You are a Power BI data analyst for a logistics company. You are preparing a dataset that includes a 'Shipments' table from an on-premises SQL Server. The table has columns: ShipmentID, CustomerID, ShipDate, DeliveryDate, Weight, and Cost. You need to create a calculated column that returns the delivery duration in days, but only for shipments that have been delivered. For undelivered shipments, the column should return null. You also need to ensure the column is formatted as a whole number. Which two actions should you perform in Power Query? (Choose two.)

Accepted Answer

Add a custom column with the formula: if [DeliveryDate] = null then null else Duration.Days([DeliveryDate] - [ShipDate]).. To meet the requirement, you need to create a custom column that conditionally calculates the delivery duration only when DeliveryDate is not null, using Duration.Days to get a whole number. Then, you must set the column's data type to Whole Number to ensure proper formatting. The other options either do not handle nulls, produce incorrect data types, or remove necessary rows.

Answer

Filter out rows where DeliveryDate is null before creating the calculated column.

Answer

Use the 'Date' subtraction operator directly: [DeliveryDate] - [ShipDate].

Answer

Add a conditional column that checks if DeliveryDate is null and returns null, otherwise returns DeliveryDate - ShipDate.