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

Question 1

You are designing a data model in Power BI. You have a Sales table and a Date table. The Date table should contain all dates from 2020 to 2025. What is the best practice for creating the Date table?

Accepted Answer

Create a separate Date table using CALENDAR function in DAX.. Using the CALENDAR function to create a separate Date table ensures a complete, contiguous date range from 2020 to 2025, independent of the Sales table. This is a best practice in Power BI for time intelligence, as it guarantees all dates are present for accurate year-over-year calculations and avoids gaps or missing dates that could occur if relying solely on transaction data.

Answer

Create a calculated table using VALUES from the Sales table's date column.

Answer

Use the Sales table's date column directly as the date dimension.

Answer

Enable the auto date/time option in Power BI settings.

Question 2

You are reviewing the M query above. The query connects to an Azure SQL database and filters orders after June 1, 2024. The query runs successfully in Power Query Editor, but when you close and apply, the data load fails. What is the most likely cause?

Accepted Answer

The database server is not accessible or credentials are missing.. The query runs successfully in Power Query Editor but fails during 'Close & Apply' because the editor uses a cached data preview, while the actual load requires a live connection to the Azure SQL database. The most likely cause is that the database server is inaccessible or the credentials are missing or expired at load time, which is a common connectivity issue in Power BI.

Answer

The date filter syntax is invalid.

Answer

The query has a syntax error in the let expression.

Answer

The column 'OrderDetails' does not exist.

Question 3

You are reviewing the DAX expression above. The DateTable is supposed to have a continuous date range from 2020 to 2025. However, when you use the Year column in a slicer, the data shows only 2024 values. What is the most likely cause?

Accepted Answer

The DateTable is not related to the fact table.. The slicer showing only 2024 values indicates that the DateTable is not related to the fact table. Without an active relationship, the slicer on the Year column cannot filter the fact table, so it defaults to showing only the current year (2024) or the first year in the DateTable. A proper relationship (e.g., one-to-many from DateTable[Date] to FactTable[Date]) is required for cross-filtering to work.

Answer

The ADDCOLUMNS function is not supported.

Answer

The DAX syntax for CALENDAR is incorrect.

Answer

The DateTable has too many rows.

Question 4

You are designing a Power BI data model for sales analysis. The source data includes a table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You need to support time intelligence calculations (e.g., year-to-date sales) and avoid creating a separate date table. What should you do?

Accepted Answer

Use the CALENDAR function in a calculated table to create a date table and mark it as a date table.. Time intelligence functions require a contiguous, marked date table. The only reliable way is to create a separate date table, which contradicts the stem's constraint. Auto date/time (B) creates hidden tables that cannot be used by DAX time intelligence. Therefore, as written, the question has no correct answer.

Answer

Enable the auto date/time option in Power BI Desktop settings.

Answer

Use the OrderDate column directly in time intelligence measures.

Answer

Create a calculated column for fiscal year using the OrderDate column.

Question 5

You need to create a Power BI data model that includes a date dimension. The source data contains a table with a Date column covering 2015-2025. You want to ensure that all dates in the model have a contiguous range for time intelligence. What should you do?

Accepted Answer

Create a calculated table using CALENDAR to generate a continuous date range and mark it as a date table.. The CALENDAR function creates a calculated table (not a column) that generates a contiguous range of dates, which is essential for accurate time intelligence calculations such as YTD or QoQ. After creating this calculated table, you can mark it as a date table to enable DAX time intelligence functions. Although the option wording mistakenly says 'calculated column', the intent is to create a separate date table using CALENDAR, which is the proper method to ensure no gaps in the date dimension.

Answer

Hide the existing Date column and rely on auto date/time.

Answer

Add a calculated column for year and month from the existing Date column.

Answer

Use the existing Date column as the date table and mark it as a date table.

Question 6

You are preparing data for a Power BI report. The source data has a column 'OrderDate' with date values. You need to create a date hierarchy for drill-down (Year, Quarter, Month). Which TWO actions are required?

Accepted Answer

Create a separate date table with a continuous range of dates.. Option A is correct because a dedicated date table containing a continuous, unbroken range of dates is the foundation for a reliable date hierarchy and time intelligence in Power BI; a continuous range avoids gaps that break drill-down and DAX time functions. Option D is correct because marking that table as a date table in Power BI Desktop (via Table tools > Mark as date table) designates its unique date column as the official date key, enabling proper relationship filtering and built-in time intelligence. Options B, C, and E are not required: auto date/time (B) generates hidden per-column date hierarchies and is generally discouraged when you have an explicit date table; sorting Month by MonthNumber (C) is a useful presentation tweak but not a required step for creating the Year/Quarter/Month hierarchy; and calculated columns for Year, Quarter, and Month (E) are optional, since the hierarchy can be built directly from the date column's built-in attributes or created later as needed.

Answer

Enable auto date/time in Power BI Desktop.

Answer

Sort the Month column by MonthNumber to ensure proper order.

Answer

Add calculated columns for Year, Quarter, and Month to the date table.

Question 7

You are connecting Power BI to an Azure SQL Database. You need to ensure that the connection uses the most secure authentication method available. Which THREE options should you consider? (Choose three.)

Accepted Answer

Use a service principal with Microsoft Entra ID.. Options C, D, and E are correct because Microsoft Entra ID (formerly Azure AD) is the most secure authentication framework for Azure SQL Database, supporting modern token-based authentication instead of static credentials. C: A service principal with Microsoft Entra ID is correct because it enables non-interactive, certificate- or secret-based app authentication with scoped permissions and no embedded user passwords. D: Microsoft Entra ID authentication is correct because it provides centralized identity management, conditional access, MFA, and token-based access to Azure SQL Database. E: A managed identity for the Power BI service is correct because it eliminates credential storage entirely by letting the Power BI service authenticate through its Azure-assigned identity. Options A and B are not marked correct: Windows authentication is not natively supported for Azure SQL Database in the same way as on-premises SQL Server, and SQL Server authentication with a strong password still relies on static credentials that are less secure than Entra ID-based methods.

Answer

Use Windows authentication.

Answer

Use SQL Server authentication with a strong password.

Question 8

You have a Power Query transformation that combines data from multiple Excel files in a folder. Each file has a different structure. You need to identify which TWO actions will help standardize the data before combining. (Choose two.)

Accepted Answer

Use the Combine Files function and select a sample file to define the transformation.. Option C is correct because the Combine Files function in Power Query lets you pick a sample file whose structure defines the transformation applied to every file in the folder, which is the standard way to standardize heterogeneous files before appending them. Option D is correct because promoting the first row as headers in each file ensures that columns are consistently named and typed across all files, which is essential when the files have different structures and must be aligned before combining. Option A is not appropriate because filtering null rows removes data rather than standardizing structure, and it does not reconcile differing schemas. Option B is wrong because merging files before cleaning would concatenate mismatched structures and produce errors or misaligned columns. Option E is wrong because removing columns from only a single file does not standardize the other files in the folder.

Answer

Filter out rows with null values.

Answer

Merge all files into one table before cleaning.

Answer

Remove all columns except the ones you need from a single file.

Question 9

Refer to the exhibit. You are configuring a Power BI dataset with row-level security (RLS) using a JSON policy. The exhibit shows an RLS configuration. A user 'analyst@contoso.com' has access to the 'Orders' table. However, when the user views the report, no data is displayed. What is the most likely cause?

Accepted Answer

The RLS policy is missing a filter expression to allow rows.. The RLS policy shown in the exhibit lacks a filter expression that defines which rows the user is allowed to see. In Power BI, a row-level security policy must include a DAX filter that returns a table of allowed rows; without it, the policy effectively denies all rows to the user. The user 'analyst@contoso.com' has access to the 'Orders' table, but the missing filter means no rows pass the security check, resulting in an empty report.

Answer

The user's email domain does not match the data source.

Answer

The connection string uses Integrated Security, which requires a gateway.

Answer

The table name 'Orders' is misspelled.

Question 10

Refer to the exhibit. You are reviewing the privacy levels for a Power BI semantic model that combines data from an on-premises SQL Server (SalesDB) and a local CSV file (MarketingCSV). The SQL Server data source has privacy level 'Organizational' and the CSV file has 'Private'. When you refresh the model, you encounter an error: 'A privacy violation occurred. Please check your privacy settings.' What is the most likely cause?

Accepted Answer

The CSV file's privacy level is set to 'Private', which prevents it from being combined with 'Organizational' sources.. Power BI enforces privacy level restrictions when combining data from different sources. The CSV file's 'Private' privacy level prevents it from being combined with the SQL Server data source set to 'Organizational', as Power BI will not merge data across these boundaries to avoid leaking private data to organizational sources. This results in the 'privacy violation' error during refresh.

Answer

The on-premises data gateway is not configured for the CSV file.

Answer

The SQL Server connection string uses Integrated Security, which is not supported when combining data.

Answer

The SQL Server table is partitioned, causing a conflict with the CSV file.

Question 11

You are using Power Query to combine data from multiple Excel files stored in a SharePoint Online document library. Each file has the same structure. You need to ensure that the query automatically includes new files added to the library without manual updates. Which approach should you use?

Accepted Answer

Use 'Get Data from SharePoint Online Folder' and then combine files using 'Combine & Transform Data'.. The 'Get Data from SharePoint Online Folder' connector in Power Query retrieves metadata for all files in the folder, and the 'Combine & Transform Data' action automatically applies a sample file transformation to all files. When new files are added to the library, refreshing the query will include them without manual intervention, as the connector dynamically reads the folder contents.

Answer

Use 'Get Data from Excel' and specify each file path manually.

Answer

Use 'Get Data from SQL Server' and write a query to read files.

Answer

Use 'Get Data from SharePoint Online List' and then expand the file content.

Question 12

You are importing data from a REST API that returns JSON. The JSON structure includes nested arrays. You need to transform the data into a flat table structure in Power Query. Which transform should you apply?

Accepted Answer

Use 'Expand to New Rows' on the list columns. The 'Expand to New Rows' transform (found under the 'Transform' tab in Power Query) is specifically designed to flatten nested list columns—such as arrays within JSON—by creating a new row for each element in the list. This converts the hierarchical JSON structure into a flat table, which is the required outcome for further analysis in Power BI.

Answer

Use 'Merge Queries' to combine the nested data

Answer

Use 'Pivot Column' on the nested columns

Answer

Use 'Unpivot Columns' on the record columns

Question 13

Refer to the exhibit. You have the above Power Query M expression. You notice that the query is taking a long time to load. You suspect that query folding is not occurring for the filter on the Year column. What is the most likely reason?

Accepted Answer

The Year column is stored as text in the database, and Power Query cannot fold the numeric comparison.. When the Year column is stored as text in the database, Power Query cannot fold a numeric comparison (e.g., `Year > 2020`) into the source query. Query folding requires data type compatibility between the filter predicate and the source column; a text-to-integer comparison forces Power Query to fetch all rows and apply the filter locally, causing slow performance.

Answer

The Year column is of type datetime, but the filter is an integer.

Answer

The query uses a native SQL query instead of a table reference.

Answer

The data source is using DirectQuery mode, which prevents folding.

Question 14

You are examining a Power BI dataflow configuration. The dataflow is scheduled to refresh the 'Sales' entity daily and the 'Product' entity hourly. However, the 'Product' entity refresh fails every hour with an error indicating the SharePoint list data source is not accessible. You need to diagnose the issue. What is the most likely cause?

Accepted Answer

The refresh interval is too frequent for the incremental granularity of 'Day'. In Power BI, a dataflow's refresh schedule is configured at the dataflow level and applies to all entities within it. You cannot refresh the 'Sales' entity daily and the 'Product' entity hourly within the same dataflow. If different schedules are needed, separate dataflows must be created. Therefore, the premise itself is invalid, and none of the provided options accurately diagnose a real issue.

Answer

Incremental refresh is not supported for SharePoint list data sources

Answer

The workspace ID is missing from the dataflow configuration

Answer

The on-premises data gateway is not configured for the SharePoint list

Question 15

Which TWO data source types can be used with Power BI dataflows?

Accepted Answer

Power BI dataset. Power BI dataflows support connecting to Power BI datasets (C), which allows a dataflow to reference an existing published dataset as its source, enabling reuse of already-modeled data. SharePoint Online lists (D) are supported because dataflows can connect to SharePoint Online via the SharePoint connector, which is one of the standard cloud connectors available in dataflows. Azure SQL Database (E) is also supported, as dataflows can use the Azure SQL Database connector to ingest data from cloud-hosted SQL databases. Exchange Online mailbox (A) is not a supported dataflow source because mailbox content is accessed through Exchange-specific connectors not available in dataflows. PDF file (B) is not supported as a dataflow source since dataflows cannot directly ingest unstructured PDF files; PDF data would need to be extracted and staged in a supported source first.

Answer

Exchange Online mailbox

Answer

PDF file