PL-300 Practice Questions

Columns from 2018 and 2019 Transactions: When combining files in Power Query, the structure of the Sample File is used as a template for all other files. In this scenario, the "First file" is selected as the sample. Given the naming convention (Transactions201801.csv, etc.), the first file is a 2018 file with the older column layout. Therefore, the final combined query will only include the columns present in that 2018 sample file. Any extra columns that exist only in the 2019 files will be ignored.

Data Type Detection and Import Times: The "Data Type Detection" setting determines how much data Power Query analyzes to automatically assign data types (e.g., Text, Number, Date). Scanning the "entire dataset" is more thorough but requires processing every row. Changing this to "Based on first 200 rows" significantly reduces the amount of data that needs to be scanned, which speeds up the data profiling step and improves the overall import time.

Microsoft Power Query Documentation: The process of combining files is detailed in the official documentation, which explains that a sample query is created based on the selected sample file. This sample query defines the transformations, including the column structure, that are applied to every file in the folder.

See the section "The Combine files experience" in the article Combine files overview from the Microsoft Power Query documentation.

Microsoft Power BI Documentation on Query Performance: Best practices for data loading in Power BI often highlight that reducing the scope of automatic operations like data type detection can improve performance.

The principle is covered in performance optimization guides which state that Power Query's automatic type detection can slow down query evaluation, and limiting its scope (e.g., to the first N rows) or disabling it can improve refresh speeds. See data loading best practices in official Power BI guidance.

SalesRowID and AuditID: The primary goal is basket analysis, which focuses on identifying product co-occurrence within transactions. A transaction is identified by SalesOrderNumber. The SalesRowID is a unique row identifier, and AuditID tracks data loading. Neither of these columns is needed to group products by SalesOrderNumber. Removing them can optimize the model's size and performance without affecting the required analysis.

OrderDateKey and OrderDate: While date information can be useful for trend analysis (e.g., seasonality of product baskets), having both OrderDateKey (an integer surrogate key for a date dimension) and OrderDate (a datetime column) is redundant. Only one is necessary to link to a date dimension or to filter by date. Best practice would be to use the OrderDateKey to relate to a dedicated Date dimension table. Therefore, both are not necessary.

TaxAmt: Basket analysis is concerned with the combination of products (ProductKey) within an order (SalesOrderNumber), not the financial details of the transaction. The precision of the TaxAmt column is irrelevant to determining which products were purchased together. The analysis would function correctly even if this column were rounded or removed entirely.

Data Modeling for BI: According to standard data warehouse modeling principles, columns that are purely for operational tracking or ETL auditing (like AuditID and SalesRowID) are often excluded from analytical models to improve efficiency and clarity. The focus is on dimensional attributes and measures relevant to the business question.

Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). Wiley. (See Chapter 2 for principles on designing fact tables and choosing relevant columns).

Redundancy in Data Models: Storing the same information in multiple columns (like OrderDateKey and OrderDate) introduces redundancy and increases model size unnecessarily. In dimensional modeling, a single surrogate key (OrderDateKey) is preferred to join to a date dimension.

Microsoft Power BI Documentation. (n.d.). Understand star schema and the importance for Power BI. Retrieved from the official Microsoft Learn website. This documentation emphasizes creating lean models by removing unnecessary columns and avoiding redundancy.

The objective is to create a date table that includes 10 full calendar years, ending with the current year.

1. var var1 = YEAR(TODAY()): This line defines a variable named var1.

• The TODAY() function returns the current date.
• The YEAR() function extracts the year from that date.
• So, var1 will hold the integer value of the current year (e.g., 2025).

1. return CALENDAR(...): This line uses the CALENDAR function to generate the date table.

• The CALENDAR function requires a start date and an end date to create a continuous list of dates between them.
• The start date is DATE(var1 - 9, 01, 01), which translates to January 1st, nine years before the current year.
• The end date is DATE(var1, 12, 31), which is December 31st of the current year.

This configuration correctly generates a table spanning exactly 10 full years (e.g., from January 1, 2016, to December 31, 2025).

Microsoft Documentation on the CALENDAR function: The official documentation describes CALENDAR(, ) as the function that "Returns a table with a single column named 'Date' that contains a contiguous set of dates."

Microsoft Documentation on the YEAR function: This source specifies that the YEAR() function "Returns the year of a date as a four-digit integer."

Microsoft Documentation on the TODAY function: The documentation explains that TODAY() "Returns the current date." It is commonly nested within other functions to work with the current date, as shown in this example.

To meet the requirements, you must first select the line chart visual to make it active. Next, you need to use the Analytics pane in Power BI Desktop, which allows you to add dynamic reference lines to visuals. Adding an Average line will create a horizontal line across the chart representing the mean order count for the period. This visually distinguishes the months where the order count was above the mean. Finally, to display the specific value of the mean, you must enable the data label for the newly created average line.

Microsoft Learn, Power BI Documentation: In the "Use the Analytics pane in Power BI Desktop" article, the procedure for adding reference lines is outlined. It states, "With the Analytics pane, you can add dynamic reference lines to visuals... To see the available reference lines for a visual, first select the visual. Then select the magnifying glass icon from the Visualizations pane. The following image shows the Analytics pane for a line chart." It further details how to add an Average line and its associated options, including the data label.

Source: Microsoft Learn. (2023). Use the Analytics pane in Power BI Desktop. "Reference lines" section.

The Private privacy level is the most restrictive. It prevents the Power Query engine from sharing or "folding" data from this source into any other data source. This is required for the "Employee review data" because it is sensitive and explicitly must not be folded into other sources.

The Organizational privacy level is designed for internal data that can be shared with other sources within the same organization or with public sources, but not with private sources. This fits the "Sales opportunities" data, which is less sensitive and needs to be available internally.

Microsoft Power BI Documentation: "Power BI Desktop privacy levels". This document explicitly defines the behavior of each level.

Private: "Contains sensitive or confidential information. A private data source or query can't be folded into any other data sources." This directly corresponds to the requirement for "Employee review data".

Organizational: "Limits the visibility of a data source to a trusted group of people in your organization. An organizational data source or query can be folded into any other organizational data sources and public data sources." This matches the "internally available" requirement for "Sales opportunities".

To minimize maintenance, you should reference the All Sales query to create the Sales Fact, Product Dimension, and Customer Dimension queries. Referencing ensures that any changes made to the source All Sales query (e.g., data source changes, initial transformation steps) are automatically propagated to the dependent queries. Duplicating would create independent copies, requiring manual updates in multiple places.

The All Sales query acts as a staging or intermediate query. Its sole purpose is to be a source for other queries that will form the final data model. To create an efficient data model and avoid loading redundant data, you should disable the data load for this staging query by clearing the Enable load option. The Sales Fact and dimension tables, which are derived from it, must have 'Enable load' selected to be part of the final model.

Microsoft Power BI Documentation: "In Power Query, you can duplicate queries or reference queries. [...] Referencing a query creates a new query that uses the results of the original query. Any changes you make to the original query are reflected in the new query." This directly supports using 'Reference' for minimizing maintenance. The documentation also states, "You can use this option [Enable load] for intermediate queries that you don't want to load into the Power BI model."

Source: Microsoft Docs, "Manage queries in Power BI Desktop".

Microsoft Power BI Documentation: On data modeling best practices, it is recommended to "Disable query load for queries that aren't needed. [...] If a query is used as an intermediate source for other queries, you might not need to load it into the model. You can disable the load by clearing Enable load in the Properties pane for the query." This confirms that staging queries like All Sales should not be loaded into the final model.

Source: Microsoft Docs, "Data reduction techniques for Import modeling".

To meet the reporting requirement of counting the total number of sales, a measure is the correct DAX object to create. Measures are designed for aggregations and are calculated dynamically at query time based on the filter context of the report (e.g., slicers, visuals). The COUNTROWS(Sales) function is the most efficient and direct method to count the total number of rows in the 'Sales' table, where each row represents a distinct sales transaction. This approach provides an accurate, context-aware total for reporting visuals.

A. Summing an identifier column (salesid) is a meaningless calculation that does not represent a valid business metric.

B. A calculated column is inefficient for this purpose as it would store the same total count in every row, unnecessarily increasing model size.

D. This option combines two errors: performing a meaningless aggregation (SUM on an ID) and using an inefficient calculated column for a report-level aggregation.

1. Microsoft Learn. (2023). Use DAX in Power BI Desktop. In the "Tutorial: Create your own measures in Power BI Desktop" section

it is explained that measures are used for creating dynamic aggregation calculations. The example for counting order lines uses COUNTROWS('Sales')

directly supporting the chosen answer.

Reference: Module "Model data in Power BI"

Unit "Create DAX calculations in Power BI Desktop"

Section "Tutorial: Create your own measures in Power BI Desktop".

2. Microsoft Docs. (2023). COUNTROWS function (DAX). The official documentation states

"This function counts the number of rows in the specified table

or in a table defined by an expression." This confirms it is the correct function for counting transactions.

Reference: Data Analysis Expressions (DAX) reference

COUNTROWS function documentation.

3. Microsoft Learn. (2023). Calculated columns vs. measures. This documentation explicitly contrasts the two

stating

"Measures are used when you display resulting values in a report... that reflect user selections." This confirms a measure is the correct choice over a calculated column for this reporting scenario.

Reference: Module "Model data in Power BI"

Unit "Introduction to creating DAX calculations in Power BI Desktop"

Section "Calculated columns vs. measures".

The UnitsInStock column has its Summarize by property set to None. When a numeric column with this setting is added to a table visual, Power BI does not aggregate the data. Instead, it displays each individual value on a separate row. Since the column has 75 non-null values, the table will display 75 rows.

Changing the Summarize by setting from 'None' to an aggregation function (such as Sum, Average, or Count) alters this behavior. When the column is subsequently added to a table, Power BI will perform the specified calculation and display a single, aggregated result (assuming no other grouping columns are present). This changes the output from 75 individual rows to a single summary row, thus reducing the number of rows.

Microsoft Power BI Documentation: In the "Use aggregation in Power BI Desktop" article, it states: "If you set the summarization to Do not summarize, then whenever you add that column to a visual, Power BI will simply show each of its values." This confirms that without summarization, all individual values are listed.

Source: Microsoft Learn, Power BI Documentation, "Use aggregation (sum, average, etc.) in Power BI Desktop," Section: "Change the aggregation type."

Microsoft Power BI Documentation: The default behavior for numeric fields is aggregation. As explained in the documentation, "When you add a numeric field...to a visual in a Power BI report, it's aggregated by default. It might be a sum, average, count...". Enabling summarization (by changing it from 'None') causes the data to be aggregated into fewer rows, typically one if it's the only field in the values well.

Source: Microsoft Learn, Power BI Documentation, "Intro to aggregations in Power BI," Section: "Why use aggregations?"

The "Sync slicers" feature in Power BI is designed for this exact scenario. It allows a selection made in a slicer on one page to be propagated to corresponding slicers on other specified pages. By selecting the slicer on Page1, opening the "Sync slicers" pane, and checking the "Sync" box for Page1 and Page3 only, you ensure that any filter applied on Page1 is mirrored on Page3. The slicer on Page2 will remain independent because its "Sync" checkbox will be left unchecked. This directly achieves the requirement of synchronizing filters between Page1 and Page3 while excluding Page2.

B. Modifying interactions controls how visuals filter each other on the same page, not how slicers behave across different pages.

C. Visibility only determines if a slicer is seen on a page. A hidden slicer can still be synced and apply its filter context to the page.

1. Microsoft Power BI Documentation

"Slicers in Power BI": Under the section "Sync and use slicers on other pages

" the documentation explains: "You can set a slicer to affect visuals on other pages... In the Sync slicers pane

you can select to which pages the slicer visibility and filtering should apply." This directly supports using the Sync slicers pane to control filtering across specific pages.

Source: https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicers-on-other-pages

2. Microsoft Learn

"Design a report in Power BI": In the module "Add report navigation

" the unit "Add slicers to a report" details the process. It states

"By default

slicers filter all visuals on the report page. You can use the Sync slicers pane to control which pages a slicer will be visible on and which pages it will filter." This confirms that synchronization is the correct mechanism for cross-page filtering control.

Source: https://learn.microsoft.com/en-us/training/modules/design-reports-power-bi/5-add-slicers

3. Microsoft Power BI Documentation

"Create and use slicers in Power BI Desktop": This document provides a step-by-step guide. In the "Sync slicers" section

it explicitly shows the pane with columns for page name

visibility

and sync. It instructs

"In the Sync slicers pane

select the Sync checkbox for the pages that you want the slicer to be in sync with." This demonstrates the precise action required by the correct answer.

Source: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-slicers#sync-slicers

The DAX PATH function builds the hierarchical chain from the current row (child) to the top-most ancestor (CEO).

PATHLENGTH then returns the number of IDs contained in that chain.

Because this count includes the employee himself/herself, subtracting 1 gives the true number of managerial “steps” (levels) between the employee and the CEO.

Therefore

LevelsToCEO = PATHLENGTH ( PATH ( Employees[EmployeeID] , Employees[ParentEmployeeID] ) ) – 1

returns 0 for the CEO, 1 for a direct report, 2 for a report-of-a-report, and so on—exactly what the requirement asks.

A. Returns PATHLENGTH without –1, so over-counts by one and fails the stated level definition.

C. PATHITEM extracts a single ancestor ID, not the count of levels, so gives one value instead of a depth measure.

D. Uses a non-existent or irrelevant function/syntax (does not build a parent-child path), thus cannot compute the hierarchy depth.

1. Microsoft Learn – “PATH function (DAX)”

Examples section: explains parent-child chains. https://learn.microsoft.com/en-us/dax/path-function-dax

2. Microsoft Learn – “PATHLENGTH function (DAX)”

Remarks 1 & Example 1: shows PATHLENGTH minus 1 to measure distance to root. https://learn.microsoft.com/en-us/dax/pathlength-function-dax

3. Ferrari

A. & Russo

M. (2023). The Definitive Guide to DAX

2nd ed.

Pearson. pp. 466-468: discussion of PATH/PATHLENGTH and subtracting 1 for edge count.

4. Russo

M. “Hierarchies with DAX

” SQLBI Whitepaper

ver. 1.3

§2.2: computing LevelsToRoot = PATHLENGTH(PATH(…))–1.