PL-300 Practice Test 14 — 15 Questions

Question 1

You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?

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

Accepted Answer

In Power Query, apply a date filter on the source query so only current year data is imported.. Applying a date filter in Power Query at the source query level ensures that only rows from the current year are imported into the Power BI data model. This reduces the data volume from over 100 million rows to a fraction, minimizing memory usage and improving refresh performance. Power Query pushes the filter down to the Azure SQL Database using a WHERE clause in the SQL query, so only the filtered data is transferred over the network.

Answer

Import all data and then remove columns that are not needed.

Answer

Load all data and filter using a visual-level filter in the report.

Answer

Use a calculated table in DAX to filter the data.

Question 2

Which TWO methods can you use to share a Power BI report with external users who do not have a Power BI Pro license? (Choose two.)

Accepted Answer

Embed the report in a secure portal using 'Embed for your customers'.. Options A and B are correct. Option A, 'Embed for your customers,' allows you to embed reports in a secure portal and share with external users without requiring them to have a Power BI Pro license (requires Premium capacity). Option B, 'Publish to a public website (Publish to web),' creates a public URL that anyone can access, including external users without a Pro license. Option D (share directly via email) requires recipients to have a Pro license. Options C and E (export to PDF or Excel) are not interactive sharing methods and do not allow the report to be consumed as a live report.

Answer

Export the report to PDF and share the file.

Answer

Share directly via Power BI using the user's email address.

Answer

Export the report to Excel and attach it to an email.

Question 3

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 4

You are preparing data from a CSV file that has inconsistent date formats. Some rows use 'MM/dd/yyyy' and others use 'dd/MM/yyyy'. You need to parse all dates correctly. What is the best approach in Power Query?

Accepted Answer

Use the 'Replace Values' to standardize the date format, then change data type.. 'Replace Values' allows you to standardize the inconsistent date strings (e.g., swapping day and month parts) before Power Query attempts to parse them as dates. After replacing the delimiters or reordering parts, you can change the column type to 'Date' using a consistent culture (e.g., 'en-US' for MM/dd/yyyy), ensuring all rows parse correctly regardless of original format.

Answer

Use the 'Parse' -> 'Date' transformation with a specific culture.

Answer

Use the 'Split Column' by delimiter to separate date parts.

Answer

Use the 'Detect Data Type' feature to automatically identify the format.

Question 5

You are a Power BI developer at a retail company. You have a data model with a 'Sales' fact table (10 million rows) and dimension tables: 'Date', 'Customer', 'Product', 'Store'. The 'Sales' table includes columns: SalesID, DateKey, CustomerID, ProductID, StoreID, Quantity, UnitPrice, Discount, TotalAmount. The 'Product' dimension has 5,000 rows and includes columns: ProductID, ProductName, Category, SubCategory, Brand, Price. The 'Store' dimension has 200 rows and includes columns: StoreID, StoreName, Region, City, Manager. The 'Customer' dimension has 100,000 rows. The report currently has a measure 'Total Sales' = SUM(Sales[TotalAmount]) and a measure 'Total Quantity' = SUM(Sales[Quantity]). Users complain that the report is slow when filtering by multiple categories and regions simultaneously. You need to improve performance without changing the data source. Which action should you take first?

Accepted Answer

Reduce the number of columns in the Customer dimension by removing unused columns. Reducing the number of columns in the Customer dimension removes unnecessary data, which decreases model size and improves query performance. Large dimensions like Customer (100,000 rows) benefit from column reduction. Option A (aggregation table) is a valid performance technique but requires more design effort and is not the simplest first step. Option B (disabling auto date/time) can reduce model size slightly but is not as impactful as removing unused columns. Option C (Dual storage mode) may not improve performance for this scenario and could increase complexity.

Answer

Create an aggregation table for the Sales table

Answer

Disable the auto date/time feature in Power BI

Answer

Change the storage mode of the Sales table to Dual

Question 6

You are connecting to an on-premises Oracle database from Power BI Service. The gateway is installed and configured. However, the scheduled refresh fails with an error indicating that the data source credentials are invalid. Which TWO steps should you take to resolve the issue? (Choose two.)

Accepted Answer

Update the data source credentials in the gateway settings in Power BI Service.. The scheduled refresh failure indicates that the stored credentials for the on-premises Oracle data source in Power BI Service are invalid. You must update the data source credentials in the gateway settings under 'Manage gateways' in Power BI Service to provide a valid username and password that the gateway can use to authenticate against the Oracle database. Option C is correct because the gateway machine requires the Oracle client software (e.g., Oracle Data Access Components or ODP.NET) to be installed and configured, and the gateway must have network connectivity to the Oracle server; verifying these ensures the gateway can reach and authenticate with the database.

Answer

Re-publish the Power BI report from Power BI Desktop.

Answer

Edit the data source settings in Power BI Desktop and republish.

Answer

Reinstall the on-premises data gateway.

Question 7

Which TWO of the following are valid reasons to use a calculated column instead of a measure in Power BI? (Select exactly two.)

Accepted Answer

You need to use the column in a relationship.. Calculated columns are evaluated row by row and stored in the model, making them available for use in relationships. Measures, in contrast, are evaluated at query time and cannot be used to define relationships between tables in Power BI.

Answer

You need to create a hierarchy for drill-down.

Answer

You need to perform a dynamic aggregation that changes with filters.

Answer

You need to calculate a running total.

Question 8

A company has a Power BI semantic model with a table named 'Sales' that contains columns: OrderDate, ShipDate, Quantity, and Revenue. The company wants to create a measure that calculates the total revenue for orders shipped within 7 days of the order date. Which DAX expression should be used?

Accepted Answer

CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). Options A and C contain the same valid DAX expression: CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). This expression correctly uses CALCULATE to modify the filter context, applying DATEDIFF to compute the day difference between OrderDate and ShipDate, and filtering for orders shipped within 7 days. Both are correct because they are identical. Option B uses SUMX with FILTER, but direct date subtraction (Sales[ShipDate] - Sales[OrderDate]) in DAX treats dates as serial numbers with time components, which can lead to inaccurate day counts and is not recommended. Option D also uses direct date subtraction in a filter argument, which is similarly incorrect. Therefore, A and C are correct.

Answer

SUMX(FILTER(Sales, Sales[ShipDate] - Sales[OrderDate] <= 7), Sales[Revenue])

Answer

CALCULATE(SUM(Sales[Revenue]), Sales[ShipDate] - Sales[OrderDate] <= 7)

Question 9

Which TWO of the following are valid methods to combine data from multiple sources in Power BI?

Accepted Answer

Append Queries. Append Queries is a valid method to combine data from multiple sources in Power BI because it stacks rows from two or more tables into a single table, which is essential when you have similar data structures across different sources (e.g., monthly sales files). This operation is performed in Power Query Editor and corresponds to a UNION operation in SQL, making it a standard data preparation technique.

Answer

Pivot Column

Answer

Unpivot Columns

Answer

Group By

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 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 12

You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?

Accepted Answer

In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.. Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.

Answer

In Power Query Editor, split the column by comma and then use the second part.

Answer

In DAX, create a calculated column using VALUE() after removing commas.

Answer

In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.

Question 13

You have a Power BI workspace named Sales. You need to ensure that only users in the Finance security group can view reports in this workspace, while members of the Sales team can edit and share content. What should you do?

Accepted Answer

Add Finance as Viewer, Sales as Member.. Workspace roles in Power BI are designed to grant specific permissions: Viewer allows read-only access, ideal for Finance who only need to view reports; Member allows editing and sharing, which matches the Sales team's requirements. Option B is wrong because Contributor role cannot share content, which Sales needs. Option C is wrong because Admin grants full control, including managing permissions, which is unnecessary and excessive. Option D is wrong because row-level security (RLS) controls data access within reports, not workspace-level permissions.

Answer

Add Finance as Contributor, Sales as Member.

Answer

Add Finance as Viewer, Sales as Admin.

Answer

Use row-level security to restrict Finance data, add both as Member.

Question 14

A company has a fact table 'Sales' with a column 'SalesAmount' and a dimension table 'Date'. They want to create a measure that calculates the running total of sales over time. The Date table is marked as a date table. Which DAX expression is correct?

Accepted Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(Date[Date])). DATESYTD returns a set of dates from the start of the year to the latest date in the current filter context, and when wrapped in CALCULATE, it correctly computes a year-to-date running total. Since the Date table is marked as a date table, DATESYTD works seamlessly with the time intelligence functions in DAX.

Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Date), Date[Date] <= EARLIER(Date[Date])))

Answer

Running Total = TOTALYTD(SUM(Sales[SalesAmount]), Date[Date])

Answer

Running Total = SUMX(Sales, Sales[SalesAmount])

Question 15

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '1,234.56' and '(987.65)' for negative amounts. You need to transform this column into a decimal number. Which sequence of Power Query steps achieves this?

Accepted Answer

Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal. It first replaces the opening parenthesis '(' with a minus sign '-', then removes the closing parenthesis ')', then removes the comma thousands separator ',', and finally changes the data type to Decimal. This sequence ensures that the negative indicator is properly placed before the numeric value and that the string is cleanly formatted for type conversion.

Answer

Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')

Answer

Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal

Answer

Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')