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

Question 1

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])). Option C is correct because 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 2

You are importing data from a CSV file that contains a column 'OrderDate' with values in the format 'YYYY-MM-DD'. Power Query automatically detects the data type as Date. You need to ensure that the data type remains Date even if the source file later changes the date format to 'MM/DD/YYYY'. What should you do?

Accepted Answer

Set the data type to Date using the 'Change Type' step in Power Query. Option A is correct because setting the data type explicitly in Power Query forces conversion regardless of source format. Option B is wrong because changing source locale doesn't fix format changes. Option C is wrong because using sample file doesn't enforce type. Option D is wrong because split column is unnecessary.

Answer

Split the column into year, month, day and then combine

Answer

Use the 'Using Locale' transformation

Answer

Change the regional settings of the Power Query editor

Question 3

You are designing a Power BI data model for a sales analysis. The source data has a table 'Orders' with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. You also have a table 'Customers' with CustomerID, CustomerName, and 'Products' with ProductID, ProductName. You need to create a star schema. What should you do?

Accepted Answer

Keep Customers and Products as separate dimension tables, and Orders as the fact table. Option C is correct because in a star schema, a single fact table (Orders) stores quantitative measures (Quantity, UnitPrice) and foreign keys (CustomerID, ProductID) that link to dimension tables (Customers, Products) containing descriptive attributes. This design optimizes query performance by reducing joins and enabling efficient aggregation, which is a best practice for Power BI data modeling.

Answer

Split Orders into multiple fact tables by year

Answer

Create a snowflake schema by normalizing Customers and Products further

Answer

Merge Customers and Products into a single dimension table

Question 4

You have a Power BI report that uses a dataset with many columns. You want to reduce the dataset size by removing columns that are not used in any report visual. What is the best practice?

Accepted Answer

Remove the columns in Power Query Editor before loading. Option A is correct because removing columns in Power Query Editor before they are loaded into the data model physically excludes them from the dataset, reducing its size and improving performance. This is the only method that prevents unused columns from consuming memory and storage in the in-memory VertiPaq engine.

Answer

Use the Q&A feature to exclude columns

Answer

Apply report-level filters to exclude the columns

Answer

Hide the columns in the model view

Question 5

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

Accepted Answer

Filter the folder contents by 'Date Modified' before combining. Option D is correct because filtering by date modified before combining reduces data volume. Option A is wrong because combining then filtering loads all data first. Option B is wrong because sample file is for structure. Option C is wrong because merging is not for appending.

Answer

Use 'Sample File' and manually filter

Answer

Use 'Merge Queries' with the folder

Answer

Combine files using 'Combine & Load', then filter by date modified

Question 6

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. Option B is correct because 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 7

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. Option A is correct because 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 8

You have a Power BI dataset that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. You need to improve query performance. Which THREE actions should you take?

Accepted Answer

Create indexes on columns used in filters. Creating indexes on columns used in filters is correct because DirectQuery translates Power BI filter operations into SQL queries against the Azure Synapse Analytics dedicated SQL pool. Indexes on those columns accelerate row-level filtering by reducing the number of pages scanned, directly improving query response times.

Answer

Normalize the data warehouse tables

Answer

Switch the dataset to Import mode

Question 9

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

Accepted Answer

Append Queries. Append Queries and Append Queries as New are both correct because they combine rows from two or more tables with identical columns into a single table. In Power Query, 'Append' is the operation designed for stacking rows vertically, which matches the requirement of combining sheets with the same columns but different row data.

Answer

Merge Queries

Answer

Group By

Answer

Pivot Column

Question 10

You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?

Accepted Answer

Private. The privacy level setting for the data source 'SalesData' is 'Private' because the JSON snippet shows the property "privacyLevel":"Private". In Power BI, the Private privacy level restricts data from being combined with data from other sources, ensuring that sensitive data is not inadvertently shared across queries. This setting is explicitly defined in the data source configuration and is not Organizational, None, or Public.

Answer

Organizational

Answer

None

Answer

Public

Question 11

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

Accepted Answer

A table with total sales amount per customer for orders after 2023. The query filters the Sales table to keep only rows where the OrderDate is in 2024 or later (i.e., after 2023), then groups by CustomerID, summing the SalesAmount for each customer. The final output is a table with one row per customer showing their total sales amount for orders placed after 2023.

Answer

A table with total sales amount per customer for all orders

Answer

A table with all sales records after 2023

Answer

A table with all sales records after 2023, excluding Discount column

Question 12

You are reviewing a DAX query in DAX Studio. The exhibit shows a query that returns a table. What is the purpose of the SUMMARIZE function in this query?

Accepted Answer

To group sales by product category and compute total sales. Option A is correct because the SUMMARIZE function in DAX is used to group rows from a table based on one or more columns (here, Product Category) and then compute an aggregated value (Total Sales) using a SUM expression. The query returns a new table with one row per category and the corresponding total sales, which is the standard purpose of SUMMARIZE for grouping and aggregation.

Answer

To add a calculated column to the Sales table

Answer

To add a new row for total sales per category

Answer

To filter the Sales table by Product Category

Question 13

You are preparing data from a SQL Server database for a Power BI report. The database contains a table with millions of rows of sales transactions. You need to minimize the data load time and reduce the model size. What should you do?

Accepted Answer

Import only the required columns and apply row-level filters in Power Query.. Option D is correct because importing only the necessary columns and applying filters to reduce rows directly reduces the amount of data loaded into Power BI, minimizing load time and model size. Option A is wrong because DirectQuery would avoid import but not reduce load time for queries and may impact performance. Option B is wrong because Power Query folding is automatic for SQL Server but does not reduce data volume unless combined with filters. Option C is wrong because incremental refresh requires a date column and configured policy, but does not reduce initial load.

Answer

Configure incremental refresh without filtering.

Answer

Enable query folding in Power Query.

Answer

Use DirectQuery instead of Import mode.

Question 14

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.. Option A is correct because 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 15

You are combining data from multiple Excel files stored in SharePoint Online. Each file has the same structure but different data. You need to create a solution that automatically includes new files added to the SharePoint folder without manual intervention. What should you use?

Accepted Answer

Use 'Get Data from SharePoint Online Folder' and then 'Combine files' transform.. Option B is correct because the 'Combine files' transform in Power Query from a SharePoint folder automatically detects and combines all files in the folder, and when refreshed, it includes any new files. Option A is wrong because Get Data from SharePoint list does not combine files. Option C is wrong because Power Automate would require additional setup and is not native Power Query. Option D is wrong because merging queries requires manual steps each time.

Answer

Use Power Automate to copy new files to a blob storage, then import from there.

Answer

Use 'Merge queries' to append each new file manually.

Answer

Use 'Get Data from SharePoint Online List' and load each file separately.