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

Question 1

You are a data analyst for a financial services company. You have a Power BI dataset that combines data from two sources: a CSV file in SharePoint Online and an on-premises SQL Server database. The CSV file contains exchange rates that are updated daily. The SQL Server database contains transaction data. You need to ensure that the dataset can be refreshed automatically in the Power BI service. The CSV file is updated at 6:00 AM daily, and the SQL Server database is updated continuously. You have already published the report. What should you do to enable automated refresh?

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

Accepted Answer

Install and configure an on-premises data gateway, then set up a scheduled refresh.. The on-premises SQL Server database requires an on-premises data gateway to bridge the Power BI service with the local network. Even though the CSV file is in SharePoint Online, the dataset combines both sources; the gateway is mandatory for the SQL Server component. Without it, scheduled refresh cannot access the on-premises data, and the dataset will fail to refresh automatically.

Answer

Use Power Automate to refresh the dataset after the CSV is updated.

Answer

Enable incremental refresh for the SQL Server table to reduce refresh time.

Answer

Configure a scheduled refresh in the dataset settings. The gateway is not required because the CSV file is in SharePoint Online.

Question 2

You are creating a Power BI report for a marketing team. The data is stored in a folder of CSV files on a network drive that is accessible from your computer. You need to combine all CSV files into a single table in Power BI. The files have the same structure. What should you do in Power Query?

Accepted Answer

Use the 'Combine Files' transform after connecting to the folder.. Power Query's 'Combine Files' transform is specifically designed to handle multiple CSV files with identical schemas stored in a folder. When you connect to the folder as a data source, Power Query automatically generates a sample file query and a transformation function that applies the same steps to all files, then combines the results into a single table. This is the most efficient and recommended approach for this scenario, avoiding manual appending or merging.

Answer

Connect to each CSV file individually and use Append Queries.

Answer

Use Merge Queries to join the files based on a common column.

Answer

Change the data source settings to treat the folder as a single data source.

Question 3

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 4

Which TWO actions can improve data refresh performance in Power BI?

Accepted Answer

Disable load for intermediate queries used only for reference.. Disabling load for intermediate queries used only as reference steps prevents Power BI from materializing those tables in the data model. This reduces memory consumption and refresh time, as the engine skips loading data that isn't needed for reports or further transformations.

Answer

Merge all queries into a single query.

Answer

Add calculated columns in Power Query instead of DAX.

Answer

Keep all columns from the source data to avoid re-importing.

Question 5

A company has a Power BI dataset that imports data from a SQL Server database. The dataset includes a table with 10 million rows. The data model uses a single table and does not include any calculated columns or measures. The report users report that the dataset refresh takes too long. Which action should you take to improve refresh performance?

Accepted Answer

Remove unused columns from the table in Power Query.. Removing unused columns from the table in Power Query reduces the amount of data loaded into the Power BI dataset. With 10 million rows, every unnecessary column adds significant I/O and memory overhead during refresh. This directly improves refresh performance by minimizing the data volume transferred and processed.

Answer

Increase the scheduled refresh frequency to every 15 minutes.

Answer

Enable Query Folding on all steps in Power Query.

Answer

Change the storage mode to DirectQuery.

Question 6

You are preparing data for a Power BI report. You have a table that contains a 'ProductID' column with some null values. You need to ensure that the 'ProductID' column does not contain any null values in the data model. Which Power Query transformation should you apply?

Accepted Answer

Replace Values -> Replace null with a default value. Replacing null values with a default value directly ensures that the ProductID column has no nulls in the data model. This transformation can be applied to a specific column using 'Replace Values' in Power Query, where you replace null with a chosen default. Options A and B do not address null values. Option C, 'Remove Blank Rows', only removes rows where all columns are blank, so rows with data in other columns but null ProductID remain, failing the requirement.

Answer

Group By

Answer

Remove Duplicates

Answer

Remove Rows -> Remove Blank Rows

Question 7

You are creating a Power BI report from a SQL Server database that contains a table Orders with columns: OrderDate, CustomerID, ProductID, Quantity, UnitPrice. You need to build a star schema. Which THREE tables should you create? (Choose three.)

Accepted Answer

Date dimension table with date attributes.. In a star schema, dimension tables contain descriptive attributes (e.g., dates, products, customers) and are connected to a central fact table. For the Orders table, a Date dimension (B) is essential for time-based analysis, a Product dimension (C) provides product details, and a Customer dimension (D) stores customer attributes. These three dimensions normalize the data and enable efficient slicing and dicing in Power BI.

Answer

OrderDetails table with line items.

Answer

Sales fact table with measures.

Question 8

You are working on a Power BI project for a marketing department. You have a CSV file with customer survey responses. The file contains columns: CustomerID, SurveyDate, Response (text with ratings from 1 to 5), Comments (free text). The file is 10 MB. You need to load the data into Power BI and create a measure that calculates the average rating. However, when you load the file, you notice that the Response column is imported as text instead of whole number. Also, there are some rows with missing values in the Response column. You need to ensure the data is correctly typed and handle missing values appropriately. What is the best approach?

Accepted Answer

Change the data type of Response to whole number in Power Query, then filter out or replace null values.. Power Query is the designated tool for data type transformations and null handling during the load phase. Changing the Response column's data type to Whole Number in Power Query automatically converts valid text numbers and flags errors, while filtering out or replacing null values ensures clean data before the data model is built. This approach follows the best practice of performing data cleansing in Power Query rather than in DAX, which would add unnecessary overhead and complexity.

Answer

Use the 'Column from Examples' feature to create a new column with numeric values.

Answer

In Power Query, split the Response column by delimiter and then use the first part.

Answer

Use a DAX calculated column to convert text to number.

Question 9

You have a Power BI dataset that includes a date table created using CALENDAR(). You need to ensure that the date table always covers the full range of dates present in the fact table, even after new data is loaded. What should you do?

Accepted Answer

Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])). Using `CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))` dynamically computes the date range from the fact table's actual data. This ensures that when new data is loaded with dates outside the previous range, the date table automatically expands to cover the full range, maintaining referential integrity for time intelligence calculations.

Answer

Use a fixed start and end date in the CALENDAR function

Answer

Create a disconnected date table

Answer

Enable Auto Date/Time in the model

Question 10

Your Power BI dataset uses a SQL view that joins multiple tables. You notice that some columns have null values where you expect data. You suspect the view definition has a bug. How can you verify the view's output in Power Query?

Accepted Answer

Create a new query that runs the view's SQL directly against the source. Creating a new query that runs the view's SQL directly against the source in Power Query allows you to isolate and execute the exact SQL statement, bypassing any transformations or folding issues. This lets you compare the raw output from the source with the view's expected results, directly verifying if the view definition itself contains a bug. It is the most straightforward method to confirm whether the null values originate from the view or from subsequent Power Query steps.

Answer

Check the 'Table Preview' in the data model

Answer

Use 'View Native Query' in Power Query

Answer

Use 'Data Profiling' in Power Query

Question 11

You are preparing data from a SQL Server database. The table 'Sales' contains a column 'OrderDate' that includes both date and time (e.g., '2023-10-15 14:30:00'). You need to create a separate column for the time portion only. Which TWO Power Query transformations can you use?

Accepted Answer

Extract - Duration. The 'Extract - Duration' transformation in Power Query extracts the time portion from a datetime column by calculating the duration since midnight, effectively isolating the time component. Option E is correct because splitting the column by a space delimiter separates the date and time parts into two columns, allowing you to keep only the time portion. Both methods produce a time-only value suitable for analysis.

Answer

Extract - Year

Answer

Merge Columns

Answer

Format - Trim

Question 12

You are using the above KQL query as a source in Power Query for a Power BI semantic model. The query runs successfully but takes a long time to execute. You need to improve performance. What should you do?

Accepted Answer

Use the 'Run KQL command' option in Power Query to pass the query directly.. Using the 'Run KQL command' option in Power Query sends the entire KQL query directly to Azure Data Explorer (or Kusto) for execution, allowing the Kusto engine to process and filter data at the source. This minimizes data transfer and leverages Kusto's optimized query engine, significantly improving performance compared to pulling all data into Power Query for transformation.

Answer

Add additional transformations in Power Query to reduce rows.

Answer

Enable query folding to push the query to the Kusto source.

Answer

Disable query folding to improve performance.

Question 13

You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?

Accepted Answer

Create a separate date table and mark it as a date table in the model.. Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require a separate date table with a contiguous date range marked as the date table in the model. This ensures that DAX can correctly calculate time-based aggregations across all dates, even if the fact table has gaps or missing dates. Without a marked date table, these functions may return incorrect or blank results.

Answer

Use the 'Add Calendar' function in Power Query and rely on auto-date/time.

Answer

Use DirectQuery mode and rely on the SQL Server date functions.

Answer

Use the built-in date hierarchy from the fact table's date column.

Question 14

You are configuring a Power BI dataset with incremental refresh. The above JSON shows part of the M script parameters. The dataset uses a single SQL Server data source. You need to ensure that incremental refresh works correctly. What must you do?

Accepted Answer

Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.. Incremental refresh in Power BI requires two special date/time parameters named 'RangeStart' and 'RangeEnd' (case-sensitive). The JSON snippet shows a parameter named 'StartDate', which is not recognized by the incremental refresh engine. Renaming it to 'RangeStart' and adding a 'RangeEnd' parameter allows Power BI to filter the data source query dynamically during refresh, ensuring only the changed or new rows are loaded.

Answer

Change the connection string to use a SQL account instead of integrated security.

Answer

Enable query folding on the SQL Server source.

Answer

Define the parameters in the Power Query editor instead of the JSON file.

Question 15

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

Accepted Answer

Use 'Replace Errors' to replace error values with null.. 'Replace Errors' in Power Query allows you to replace error values (which occur when Power Query fails to convert an invalid date like '02/30/2023' to the Date type) with null. This preserves the rest of the data and keeps the query running without interruption, while clearly marking invalid entries for later handling or analysis.

Answer

Remove rows with errors using 'Remove Rows' > 'Remove Errors'.

Answer

Change the data type to 'Date' and ignore errors.

Answer

Filter the column to exclude rows where the date is invalid after type conversion.