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

Question 1

You are reviewing a Power BI data source configuration for an Azure SQL Database. The connection uses OAuth2 authentication. However, when scheduling a refresh, the refresh fails with an error that authentication fails. What is the most likely cause?

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

Accepted Answer

The authority URL should point to the specific Azure AD tenant, not 'common'. When using OAuth2 authentication with Azure SQL Database in Power BI, the authority URL must point to the specific Microsoft Entra ID tenant (e.g., 'https://login.microsoftonline.com/your-tenant-id') rather than the generic 'common' endpoint. The 'common' endpoint is designed for multi-tenant applications and cannot resolve the tenant-specific authentication context required for scheduled refreshes, leading to authentication failures.

Answer

The connection string has 'Encrypt=True' which is incompatible with OAuth2

Answer

The server name in the connection string is incorrect

Answer

The resource URL should be 'https://management.azure.com/'

Question 2

You are working with a large dataset in Power BI that contains sales transactions. You need to create a measure that calculates the running total of sales over time. However, the data model does not include a separate date table, and you want to minimize the model size. What is the most efficient approach?

Accepted Answer

Create a separate date table and use the TOTALYTD function in a measure.. Creating a separate date table and using TOTALYTD leverages Power BI's time intelligence functions, which require a continuous date table marked as a date table. This approach is efficient because time intelligence functions are optimized for performance and minimize model size by avoiding calculated columns or complex row-context iterations. Without a date table, TOTALYTD cannot function correctly, as it relies on a proper date hierarchy.

Answer

Write a measure using VAR and FILTER with ALL to compute running total.

Answer

Create calculated columns for Year and Month, then use SUMX with FILTER.

Answer

Use the EARLIER function in a calculated column to compute running total.

Question 3

You are importing data from a JSON file that contains nested arrays. You need to expand the arrays into separate rows while maintaining the parent information. What should you do?

Accepted Answer

In Power Query Editor, click the expand icon on the column and select 'Expand to New Rows'.. Power Query's 'Expand to New Rows' function is specifically designed to flatten nested arrays (e.g., lists or records within a JSON column) into separate rows while duplicating the parent row's other columns. This operation uses the `Table.ExpandListColumn` or `Table.ExpandRecordColumn` M function under the hood, which unpacks each element of the array into its own row, preserving the parent context.

Answer

Use the Pivot Column feature to transform the array.

Answer

Use the Merge Queries feature to combine tables.

Answer

Use the Group By feature to flatten the data.

Question 4

You have a Power BI data model that contains a fact table and a date dimension. The fact table has a column 'OrderDate' and the date dimension has a column 'Date'. You need to create a measure that calculates the total sales for the last complete month. The date dimension is marked as a date table. What DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date])). PREVIOUSMONTH returns a set of dates for the previous month relative to the last date in the current filter context. Since the date dimension is marked as a date table, time intelligence functions like PREVIOUSMONTH work correctly. CALCULATE then evaluates SUM(Sales[Amount]) over that month-long period, giving total sales for the last complete month.

Answer

CALCULATE(SUM(Sales[Amount]), DATESMTD('Date'[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), 'Date'[Date] = DATE(2023,1,1))

Answer

TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Question 5

Which TWO of the following are valid ways to create a date table in Power BI?

Accepted Answer

Using DAX function CALENDAR or CALENDARAUTO.. The DAX functions CALENDAR and CALENDARAUTO are specifically designed to generate a single-column table of dates, which can then be used as a date table. CALENDAR requires explicit start and end dates, while CALENDARAUTO automatically scans the model to determine the date range. Both are standard methods for creating a date table in Power BI.

Answer

Using the time intelligence functions like TOTALYTD.

Answer

Marking an existing table as a date table.

Answer

Enabling the auto date/time feature.

Question 6

You are a business analyst at a marketing agency. You receive a weekly export from a web analytics platform as a CSV file. The file contains columns: Date, Campaign, Impressions, Clicks, Conversions. You need to create a Power BI report that shows trends over time, with the ability to filter by campaign. The CSV file is updated weekly and you want to automate the refresh. You also want to ensure that any new campaigns are automatically included in the report. You have limited experience with Power BI. What should you do?

Accepted Answer

Create a Power BI dataflow that reads the CSV file from a SharePoint folder, transforms the data (e.g., change data types), and then load the dataflow as a dataset into Power BI. Schedule the dataflow to refresh weekly.. It uses a Power BI dataflow to read the CSV from a SharePoint folder, which supports scheduled refresh without a personal gateway. The dataflow can automatically include new campaigns as they appear in the folder, and the scheduled weekly refresh ensures the report stays up to date. This approach is ideal for users with limited Power BI experience because dataflows handle transformation and refresh centrally in the Power BI Service.

Answer

Use Power Query to read the CSV from a local folder, but schedule the refresh using a personal gateway.

Answer

Import the CSV file directly into Power BI Desktop, create the report, and publish to Power BI Service. Manually re-import the CSV each week.

Answer

Enable incremental refresh on the dataset to only load new data each week.

Question 7

You are creating a Power BI dataset from a SQL Server data warehouse. The warehouse contains a fact table with 500 million rows and dimension tables. You need to minimize the data refresh time while ensuring that the dataset meets the reporting requirements. Which approach should you recommend?

Accepted Answer

Use Import mode but filter rows and reduce columns in Power Query to only those needed.. Importing only the necessary columns and rows reduces the data volume, which directly minimizes refresh time. With 500 million rows, Import mode is generally faster than DirectQuery for large fact tables in Power BI, as it avoids querying the source on every interaction. Filtering and column reduction in Power Query ensures the dataset remains lean while meeting reporting requirements.

Answer

Create a composite model using DirectQuery for the fact table and Import for dimensions.

Answer

Use DirectQuery mode for the dataset.

Answer

Configure incremental refresh on the fact table.

Question 8

You are combining CSV files from a folder. The code above returns an error because some CSV files have inconsistent column names and data types. You need to modify the solution to handle variations in column structure. What should you do?

Accepted Answer

Use the Folder connector's 'Combine Files' transform, which handles schema merging automatically.. To handle variations in column structure, you must edit the generated Combine Files function to align schemas dynamically, e.g., by using Table.ColumnNames and Table.TransformColumnTypes, or use a custom function that reads all files and combines them with schema normalization. None of the provided options correctly describe this approach.

Answer

Change the delimiter to a semicolon and reconfigure the CSV parser.

Answer

Use Table.RemoveRows to remove rows that cause errors.

Answer

Add a custom column to each file to standardize data types before combining.

Question 9

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.. In a calculated column, SUM(Sales[Amount]) does not aggregate only the current row; instead, it calculates the sum of the entire Amount column (grand total) for every row. Because this value is identical across all rows, the RANKX function assigns a rank of 1 to each row. To achieve proper ranking, you must use a measure that respects filter context or explicitly modify the expression to remove row context using functions like ALL().

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 10

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.. '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.

Question 11

You have a Power BI report that uses a date table connected to a fact table. You need to ensure that all dates in the fact table are covered by the date table. Which relationship property should you configure?

Accepted Answer

Assume referential integrity. The 'Assume referential integrity' property, when enabled, tells Power BI that every value in the foreign key column of the fact table exists in the primary key column of the date table. This ensures that all dates in the fact table are covered by the date table, allowing Power BI to use more efficient storage and query execution (e.g., INNER JOIN semantics) rather than a full OUTER JOIN.

Answer

Make this relationship active

Answer

Cardinality

Answer

Cross filter direction

Question 12

You need to combine two tables in Power Query: 'Sales2023' (columns: Date, ProductID, Amount) and 'Sales2024' (columns: Date, ProductID, Amount). Which TWO transformations can be used to append rows from Sales2024 to Sales2023?

Accepted Answer

Append Queries. Append Queries is the dedicated Power Query transformation to stack rows from one table onto another, making Option D the only correct method. Merge Queries with Full Outer join combines columns based on matching keys, not rows; it does not append rows unless the key columns are identical and no matching rows exist, which is not a reliable or intended method. Option A (Combine Files) imports multiple files, not appending existing tables. Option B (UNION in DAX) applies after data is loaded, not in Power Query. Option E (Group By) aggregates rows. Therefore, only Append Queries is correct.

Answer

Combine Files

Answer

Use UNION function in DAX

Answer

Merge Queries as New with Full Outer join

Answer

Group By

Question 13

You are importing data from a folder containing multiple Excel files with the same structure. You use Power Query's 'Combine Files' feature. Which TWO statements about this process are correct?

Accepted Answer

It uses the first file as a template for transformation.. When you use Power Query's 'Combine Files' feature, it uses the first file as a template to infer the schema and transformations. This sample file query defines how each subsequent file is processed, ensuring consistent column types and transformations across all files.

Answer

It automatically removes duplicate rows across files.

Answer

You can change the transformation order after combining.

Answer

It automatically creates relationships between files.

Question 14

You are preparing data for a star schema. Which THREE types of tables are typically included in a star schema?

Accepted Answer

Dimension tables. In a star schema, the core tables are fact tables (E), which store quantitative measures and foreign keys to the surrounding dimensions, and dimension tables (C), which hold descriptive attributes used for slicing and grouping those measures. A date table (D) is a specialized, commonly required dimension that provides a continuous calendar of dates with attributes like year, quarter, and month, enabling consistent time intelligence and time-based analysis across the fact data. Staging tables (A) belong to the ETL/ELT loading layer that temporarily holds source data before it is transformed into the star schema, so they are not part of the schema itself. Bridge tables (B) are used in snowflake schemas or many-to-many relationship designs to resolve relationships between tables, which is outside the typical star schema structure.

Answer

Staging tables

Answer

Bridge tables

Question 15

You are a Power BI administrator at Contoso Ltd. The company has a Power BI tenant with Premium capacity. A data analyst has published a dataset that uses DirectQuery mode to a SQL Server database. Users report that the report is slow. You need to improve query performance without changing the data source. You decide to implement a composite model by adding an imported table for a frequently used dimension. After importing, you notice that the relationship between the imported table and the DirectQuery table is Many-to-Many, which causes ambiguity. What should you do to resolve the ambiguity and ensure correct filtering?

Accepted Answer

Create a bridge table with a calculated column to create a one-to-many relationship.. A bridge table resolves the Many-to-Many ambiguity by creating a separate table that contains only the unique key values from both sides, enabling a star-like schema with one-to-many relationships. This ensures that filters from the imported dimension table propagate correctly to the DirectQuery fact table without ambiguity.

Answer

Remove the imported table and use only DirectQuery.

Answer

Use the CROSSFILTER function to ignore the relationship.

Answer

Change the relationship to One-to-Many (1:*).