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

Question 1

You are cleaning a column that contains numbers stored as text, with occasional leading/trailing spaces and currency symbols. You apply the function above to the column. However, some rows return null even though the original text appears to be a valid number, such as '$ 1,234.56'. What is the most likely cause?

Accepted Answer

The function does not remove commas and currency symbols before conversion.. Option D is correct because the function shown (likely a custom M function or Power Query transformation) does not strip commas and currency symbols before attempting conversion. When the input '$ 1,234.56' is passed directly to a number conversion like Number.From or Number.FromText, the presence of the dollar sign, spaces, and comma causes the conversion to fail, returning null. The try...otherwise block only catches errors but does not clean the text, so the conversion still fails silently.

Answer

The try...otherwise block is not catching parsing errors.

Answer

Text.Clean removes necessary decimal separators.

Answer

The function is applied to the wrong data type column.

Question 2

You are merging two queries in Power Query. Query 'Orders' contains columns: OrderID, CustomerID, OrderDate. Query 'Customers' contains columns: CustomerID, CustomerName, Segment. You need to add the CustomerName to the Orders query. The relationship between Orders and Customers is many-to-one. Which join kind should you use?

Accepted Answer

Left Outer. The goal is to retain all rows from the Orders table while adding CustomerName from the Customers table. A Left Outer join returns all rows from the first (left) table and only matching rows from the second (right) table, filling non-matches with null. Since the relationship is many-to-one, each OrderID may have a matching CustomerID, and you want to keep every order even if a customer is missing — exactly what Left Outer does.

Answer

Inner

Answer

Right Outer

Answer

Full Outer

Question 3

You have a Power BI dataset that uses Import mode and refreshes daily. The source data includes a column 'LastModifiedDate'. You want to reduce the amount of data loaded during each refresh by only loading rows that have changed since the last refresh. Which feature should you configure?

Accepted Answer

Configure incremental refresh on the table using the 'LastModifiedDate' column.. Incremental refresh in Power BI allows you to filter data by a date/time column (such as 'LastModifiedDate') so that only rows that have changed since the last refresh are loaded. This reduces refresh time and data volume while still using Import mode. The feature requires a date/time column and a properly configured policy in the Power Query Editor.

Answer

Enable query folding in Power Query.

Answer

Use the 'Reduce data' option in Power Query Editor.

Answer

Change the storage mode to DirectQuery.

Question 4

You are connecting to a SharePoint folder that contains Excel workbooks. Each workbook has multiple sheets. You need to combine data from a specific sheet named 'Sales' across all workbooks. Which Power Query approach should you use?

Accepted Answer

Use the SharePoint folder connector, filter by .xlsx, then expand the Content column and filter by sheet name 'Sales'.. Option B is correct because the SharePoint folder connector retrieves all files in the folder, including Excel workbooks. By filtering for .xlsx files and then expanding the Content column, you access the binary data of each workbook. You can then filter by the 'Sales' sheet name to combine data from that specific sheet across all workbooks, which is the only approach that directly handles multiple workbooks with multiple sheets.

Answer

Use the SharePoint Online List connector and select the document library.

Answer

Use the Excel connector and specify the folder path.

Answer

Use the Web connector and provide the SharePoint site URL.

Question 5

You have a Sales table with columns: ProductID, Amount, Date. You create the calculated column above. However, the rank values are not as expected; all rows show 1. What is the most likely cause?

Accepted Answer

The expression SUM(Sales[Amount]) in a calculated column aggregates only the current row, resulting in the same value for all rows.. Option C is correct because in a calculated column, the SUM(Sales[Amount]) expression is evaluated in row context, meaning it aggregates only the current row's Amount value rather than the entire Sales table. Since every row has the same single-row aggregate, RANKX sees identical values for all rows and assigns rank 1 to each. To get correct ranking, you must use a measure or explicitly use ALL(Sales) in the expression to remove row context.

Answer

The Dense parameter forces all ranks to 1.

Answer

The DESC parameter is misspelled; it should be 'Desc'.

Answer

The RANKX function requires a measure, not a calculated column.

Question 6

You are reviewing a Power Query query that loads data from a SQL Server database. The query includes multiple steps that perform data transformation. You want to ensure that the query is optimized by pushing as many transformations as possible to the SQL Server. What should you look for?

Accepted Answer

Check for the 'Query Folding' indicators in the query editor.. Option C is correct because query folding indicators in Power Query Editor show whether transformations are being pushed to the SQL Server source. When a step shows a 'folded' icon (a table with a down arrow), it means the transformation is translated into native SQL and executed on the server, reducing data transfer and improving performance. Checking these indicators directly confirms which steps are folded and which are not, allowing you to optimize the query by reordering or rewriting steps to maximize folding.

Answer

View the native SQL query generated by Power Query.

Answer

Use the Performance Analyzer in Power BI Desktop.

Answer

Review the data preview for each step.

Question 7

You are preparing data from an Azure SQL Database. You need to ensure that sensitive columns (e.g., Social Security Numbers) are obfuscated in Power BI reports. Which TWO of the following approaches can you use? (Choose two.)

Accepted Answer

Configure dynamic data masking on the Azure SQL Database.. Option A is correct because Azure SQL Database Dynamic Data Masking (DDM) obfuscates sensitive data at the database query level, so when Power BI connects to the database, the masked values are automatically returned for unauthorized users. This is a server-side approach that does not require changes to the Power BI report or data model.

Answer

Use row-level security (RLS) in Power BI to hide sensitive columns.

Answer

Use Microsoft Purview sensitivity labels to mask data.

Answer

Apply column-level security in Power BI Desktop.

Question 8

You are connecting to an on-premises Oracle database from Power BI Service. The gateway is installed and configured. However, the scheduled refresh fails with an error indicating that the data source credentials are invalid. Which TWO steps should you take to resolve the issue? (Choose two.)

Accepted Answer

Update the data source credentials in the gateway settings in Power BI Service.. Option B is correct because the scheduled refresh failure indicates that the stored credentials for the on-premises Oracle data source in Power BI Service are invalid. You must update the data source credentials in the gateway settings under 'Manage gateways' in Power BI Service to provide a valid username and password that the gateway can use to authenticate against the Oracle database. Option C is correct because the gateway machine requires the Oracle client software (e.g., Oracle Data Access Components or ODP.NET) to be installed and configured, and the gateway must have network connectivity to the Oracle server; verifying these ensures the gateway can reach and authenticate with the database.

Answer

Re-publish the Power BI report from Power BI Desktop.

Answer

Edit the data source settings in Power BI Desktop and republish.

Answer

Reinstall the on-premises data gateway.

Question 9

You are transforming a table that contains a 'Date' column in text format (e.g., '2026-01-15'). You need to create separate columns for Year, Month, and Day. Which THREE Power Query transformations can you use? (Choose three.)

Accepted Answer

Split Column by Delimiter using '-' as the delimiter.. Option B is correct because splitting the 'Date' column by the '-' delimiter in Power Query separates the text into individual components (Year, Month, Day) in one step. This is a direct transformation for extracting parts from a date string in a standard format like 'YYYY-MM-DD'.

Answer

Unpivot the Date column.

Answer

Merge the Date column with itself.

Question 10

You are connecting to a large CSV file (10 GB) stored in Azure Blob Storage. You need to load the data into Power BI with optimal performance. Which THREE practices should you follow? (Choose three.)

Accepted Answer

Use Parquet format instead of CSV if possible.. Options A, C, and E are correct because splitting the file, filtering rows early, and using an efficient file format improve performance. Option B is incorrect because the Power BI service can handle large files, but the gateway is not needed for cloud storage. Option D is incorrect because importing all columns reduces performance; you should select only needed columns.

Answer

Import all columns and use Power BI to hide unused ones.

Answer

Use the on-premises data gateway to connect to Azure Blob Storage.

Question 11

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

Accepted Answer

Remove blank rows that result from merged cells.. Option A is correct because merged cells in Excel often introduce blank rows when the data is loaded into Power Query. Using 'Remove Blank Rows' in Power Query eliminates these extraneous rows that do not contain meaningful data, ensuring the dataset is clean and ready for transformation.

Answer

Group rows by a key column to summarize data.

Answer

Merge columns to create a single identifier.

Answer

Unpivot columns to normalize the data.

Question 12

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?

Accepted Answer

Use a composite model: DirectQuery for the current month's sales data from Azure SQL, and Import mode for historical sales (with incremental refresh) and for customer feedback (with hourly refresh). Combine SharePoint files using Power Query and load them into the model using Import mode. Set up a DirectQuery connection for near real-time.. Option C is correct because it uses a composite model to meet all requirements: DirectQuery for near real-time current-month sales (≤15-minute latency), Import mode with incremental refresh for historical sales (daily refresh), Import mode for customer feedback (hourly refresh), and Power Query to combine SharePoint Excel files into a consistent table. This approach balances real-time needs with query performance and refresh flexibility, leveraging Power BI Premium's composite model capabilities.

Answer

Use DirectQuery for all data sources and create views in Azure SQL to transform the SharePoint and Dataverse data. Use Power Query to combine SharePoint files in a view.

Answer

Import all data into Power BI using Import mode. Schedule refreshes every 15 minutes for the current month and daily for historical data.

Answer

Use Azure Data Factory to copy all data to Azure SQL Database, then connect Power BI using DirectQuery.

Question 13

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?

Accepted Answer

In Power Query, create a blank query that generates a date table using the List.Dates function with a custom column for Year, Month, etc. Load it into the model and mark it as a date table.. Option D is correct because the requirement explicitly states that the date dimension table must be created using M code in Power Query, and the List.Dates function is the appropriate M function to generate a continuous range of dates from 2010 to 2030. After creating the table with additional columns like Year and Month, you must load it into the model and mark it as a date table to enable time intelligence functions. This approach satisfies the need for a custom date dimension that is not dependent on DAX or auto-generated hierarchies.

Answer

Use DAX to create a date table using CALENDAR function in Power BI Desktop, then mark it as a date table.

Answer

Enable the 'Auto date/time' option in Power BI Desktop and hide the generated date hierarchy.

Answer

In the model view, create a relationship between PatientVisits[VisitDate] and DiagnosisLookup[DiagnosisCode].

Question 14

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?

Accepted Answer

Connect to folder, combine files, replace nulls, remove duplicates, set data types.. Option C is correct because the logical order is: connect to folder, combine files, then clean data (replace nulls, remove duplicates) and set data types. Option A is incorrect because setting data types before cleaning can cause errors. Option B is incorrect because removing duplicates before replacing nulls may remove rows that could be fixed. Option D is incorrect because removing duplicates before combining files would apply to each file individually, not across files.

Answer

Connect to folder, replace nulls in each file, combine files, remove duplicates, set data types.

Answer

Connect to folder, combine files, set data types, replace nulls, remove duplicates.

Answer

Connect to folder, combine files, remove duplicates, replace nulls, set data types.

Question 15

You are loading data from a SQL Server database into Power BI. The source table contains a column 'SalesAmount' with occasional NULL values. In the Power Query Editor, you need to replace NULLs with 0 before loading. What is the most efficient transformation?

Accepted Answer

Use 'Replace Values' to replace null with 0.. Option C is correct because 'Replace Values' in Power Query Editor is specifically designed to replace null values with a specified value, such as 0. This transformation is the most efficient and direct method for handling NULLs in a column, as it operates at the query level before data is loaded into the Power BI data model.

Answer

Use 'Replace Errors' with value 0.

Answer

Use 'Fill Down' to fill nulls with the previous value.

Answer

Use 'Change Type' to convert to a non-nullable type.

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

Exhibit

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

Exhibit