Microsoft Power BI Data Analyst Practice Test

Shared capacity restricts each semantic model to eight scheduled refreshes per day. A 30-minute interval between 08:00 and 18:00 requires 20 refreshes. Moving the workspace to a Power BI Premium capacity (or Premium Per User) increases the limit to 48 refreshes per day, enabling you to schedule the needed frequency. Incremental refresh reduces data volume but does not raise the refresh-per-day limit. DirectQuery eliminates scheduled refresh entirely but would change the report's query pattern, which isn't required here. Using a dataflow still leaves the dataset subject to the same refresh quota. Therefore, upgrading the workspace to Premium capacity is the necessary first step.

The correct answer is to remove duplicate rows and add an index column. In a star schema, the key column on the 'one' side of a one-to-many relationship must contain unique values. The best practice to resolve duplicate values in a dimension table is to remove the duplicate rows based on the business key (like 'ProductSKU') and then add an index column in Power Query. This new column acts as a surrogate key, providing a unique integer-based identifier for each row, which is optimal for model performance and relationship integrity.

Creating a composite key is a possible but less optimal solution, as joins on multiple columns or text columns are less performant than on a single integer key. Establishing a many-to-many relationship is not ideal as it is a workaround for the data quality issue, not a solution, and can lead to ambiguity and performance problems. Filtering rows from the 'Sales' fact table is incorrect as it would result in the loss of valid data and lead to inaccurate reports.

The correct DAX formula is CALCULATE(SUM('Inventory'[UnitsInStock]), LASTDATE('Date'[Date])). Here's why:

• Correct Answer: This formula uses the CALCULATE function to modify the filter context. The LASTDATE('Date'[Date]) function returns a table containing only the last date in the current filter context (e.g., the last day of a selected month). CALCULATE then applies this new single-day filter context to the SUM('Inventory'[UnitsInStock]) expression, effectively calculating the stock for only the last day of the period. This is the standard pattern for handling semi-additive measures like inventory balances.

• SUM('Inventory'[UnitsInStock]): This is incorrect because it is a fully additive measure. It would sum the 'UnitsInStock' for every day in the selected period, leading to a vastly inflated and incorrect number. For example, the inventory for a month would be the sum of 30 daily balances instead of the balance on the last day.

• AVERAGEX(VALUES('Date'[Date]), SUM('Inventory'[UnitsInStock])): This is incorrect because it calculates the average daily stock over the period, not the closing balance on the last day. While this might be a useful metric in some scenarios, it does not meet the requirement for a closing balance.

• TOTALYTD(SUM('Inventory'[UnitsInStock]), 'Date'[Date]): This is incorrect as it is a time intelligence function that calculates the cumulative (year-to-date) total. It does not isolate the value from the last day of the period; instead, it sums all values from the start of the year up to the end of the current period.

The correct process involves creating dimension tables from the main query and then using those dimensions to prepare the final fact table. First, you should create new queries that reference the original 'SalesData' query for each dimension you want to create (e.g., 'DimCustomer', 'DimProduct'). Using 'Reference' is efficient as it creates a dependency on the source query without duplicating the data in memory. In each of these new dimension queries, you isolate the relevant columns (e.g., 'CustomerName' for 'DimCustomer'), remove all other columns, and then remove duplicate rows to create a unique list of dimension members. It is a best practice to add a unique index column (surrogate key) to each dimension table. After the dimensions are created, you return to the 'SalesData' query, which will serve as your fact table. You merge this query with each new dimension table to bring in their surrogate keys. Finally, you remove the original, now-redundant descriptive columns (e.g., 'CustomerName', 'ProductCategory') from the fact table, leaving only the foreign keys and the numeric measures.

Adding a Rename Columns step right after Source converts the new header 'UnitCost' back to 'Unit Price'. Because all following steps still refer to 'Unit Price', they can execute without modification. Rebuilding the query would resolve the error but is unnecessary and time-consuming. Editing or disabling the Changed Type step would still leave later steps referencing the old column name, so those steps would also need to be changed. Switching to DirectQuery does not address missing-column errors; the schema must still match during refresh.

The Group By dialog in Advanced mode lets you add multiple aggregations in a single step. By grouping on CustomerID and adding a Sum aggregation for LineTotal and a Count Rows aggregation, Power Query generates a single Table.Group step that SQL Server can execute, so folding is preserved. Duplicating queries or using DAX would add extra steps, and buffering the table forces the data to be loaded locally, which breaks folding.

Column distribution is the only data-profiling view that surfaces both the Distinct and Unique counts directly under each column header, letting you instantly spot columns with duplicate or null values. Column quality only shows percentages of valid, error, and empty rows; it does not reveal distinct or unique counts. Column profile does include those counts but only after you select one column at a time, so it is less efficient for scanning all columns simultaneously. The Data preview (top 1,000 rows) switch merely changes the sampling range and does not add any new profiling metrics.

The correct approach is to first use the 'Replace Values' transformation to substitute the text string "N/A" with 0. Then, use 'Replace Values' again to substitute the null values with 0. After these replacements, the column only contains numerical data, so you can safely change the data type to 'Decimal Number' without introducing errors. Changing the data type to a number before replacing the "N/A" text would convert those text values into errors, which would then need to be handled. Replacing the values first is a more direct and cleaner method. The 'Remove Errors' option would result in data loss, which the requirement explicitly forbids. The 'Fill' command is used for replacing nulls with the value from the preceding or succeeding row and is not appropriate for this scenario.

The correct answer is to use the 'Split Column by Delimiter' transformation. This feature is specifically designed to parse text that is separated by a consistent character. By selecting the hyphen (-) as the delimiter, Power Query will automatically separate the 'OrderCode' column into three new columns for Region, ProductID, and Date. You can then keep the ProductID column and remove the others.

A 'Conditional Column' is incorrect because it is used for creating new values based on 'if-then-else' logical conditions, not for extracting substrings based on a delimiter.

'Replace Values' is inefficient for this scenario because the region and date codes are likely to change from row to row, which would require an impractical number of individual replacement steps.

'Unpivot Columns' is incorrect as it is a structural transformation used to convert data from a wide format to a long format; it is not used for text manipulation within a column.

Choosing Get data > Power BI semantic model creates a live connection to SalesAnalytics. Power BI Desktop does not import the data; it simply queries the shared semantic model, so only one copy of the data and model definitions exists. The live connection automatically applies any row-level security that is already defined. Connecting directly to the SQL database would bypass the vetted semantic model, potentially break security rules, and duplicate modelling effort. Downloading the PBIX moves the model to a separate file, creating maintenance overhead and risking multiple versions. Analyze in Excel generates an Excel workbook, not a Power BI Desktop report, and importing that workbook would still create a separate model copy. Therefore, only the live connection meets all requirements.

Clearing the Enable load option turns the query into a connection-only object. The query still executes when referenced by other queries, so dependent transformations continue to work, but the resulting table is not loaded into the data model. This reduces model size and avoids the time required to load that data. Clearing Include in report refresh would stop Staging_Product from refreshing at all, which could break downstream queries. Changing storage mode to DirectQuery or Dual is irrelevant for a purely intermediate query.

DirectQuery leaves the data in Azure SQL and sends a query to the database each time a visual is rendered, so the report always shows the latest rows without relying on dataset refresh. Import mode-whether full or with incremental refresh-would still depend on scheduled dataset refresh, and shared capacity limits this to eight refreshes per day, far short of the five-minute requirement. A live connection applies to Analysis Services models, not to an Azure SQL source. Therefore, DirectQuery is the only mode that delivers near real-time data under the stated licensing and capacity constraints.

The correct answer is USERELATIONSHIP. To analyze a measure by different date roles (like order date and ship date) using a single date table, you create multiple relationships, where one is active and the others are inactive. The USERELATIONSHIP function is used within a CALCULATE statement to temporarily activate an inactive relationship for the duration of that specific calculation.

• CROSSFILTER is incorrect because it modifies the filter direction of an existing active relationship; it does not activate an inactive one.
• TREATAS is incorrect. It is used to apply the filter context from one table to another where no physical relationship exists, which is different from activating a predefined inactive relationship.
• RELATEDTABLE is incorrect as it is a navigation function that returns a table of related rows from the 'many' side of a relationship, not for activating a relationship path for a calculation.

The correct approach is to set the parameter's 'Type' to 'Text', as a file path is a text string. It is also a best practice to clear the 'Enable load' selection. Parameters are typically used within other queries (like defining a data source) and do not need to be loaded into the data model as a separate table, which unchecking 'Enable load' prevents. Loading the parameter into the model would consume unnecessary memory.

The correct action is to reference the 'BaseSales' query. Referencing creates a new query that is linked to the original and uses the output of the source query as its starting point. This means any changes made to the cleaning steps in 'BaseSales' will automatically flow through to 'SalesByRegion' and 'SalesByProduct'. This centralizes the transformation logic, making the model more efficient and easier to maintain.

Duplicating the query would create two new, independent queries. Any changes in 'BaseSales' would not propagate to them, requiring manual updates in three separate places.

Merging and Appending are different operations. Merging is used to join queries column-wise (like a SQL join), and Appending is used to stack queries row-wise.

A calculated column is evaluated for every row in a table during data refresh and the result is stored in the model, so the value can be placed in slicers, rows, or columns. That makes it the correct choice for the FiscalQuarter attribute, which must be selectable in a slicer. A calculated table is created with a DAX expression that returns an entire table. Because it is stored in the model, it can participate in relationships and act as an independent dimension. Using a calculated table allows you to aggregate FactSales by customer, filter to those above $1,000,000, and then relate the resulting HighValueCustomers table to other tables. Alternative answers fail because measures cannot be used in slicers or relationships, and a calculated column cannot create a separate table that other tables can relate to.

DirectQuery leaves the data in the Azure SQL Database and stores only metadata in the Power BI semantic model, so no copy of the detailed rows is persisted in the service. Because queries are sent to the source each time a visual renders, the report can surface changes almost immediately-well inside the one-minute requirement-without waiting for scheduled or incremental refresh cycles. Import modes (with or without incremental refresh) would cache the data inside Power BI and rely on refresh jobs that cannot guarantee sub-minute latency. A live connection to Analysis Services is irrelevant here because the model resides directly in Azure SQL, and building an intermediate Analysis Services layer would still require data movement or a separate modeling environment. Therefore, DirectQuery best satisfies both the real-time visibility and the data-duplication constraint.

The correct data type is Fixed Decimal Number. This data type is specifically designed for currency values and provides precision up to four decimal places, which prevents the floating-point rounding errors that can occur with the Decimal Number type. The Decimal Number type is a floating-point number and is not suitable for financial calculations where exact precision is required. Whole Number is incorrect as it cannot store decimal values. Text is incorrect because it prevents mathematical operations.

Power BI permits only one active relationship between any two tables. To let visuals filter the Sales fact table by both OrderDateKey and ShipDateKey at the same time, each role-playing instance of the date dimension must have its own active relationship. The recommended pattern is therefore to duplicate the Date table (for example, create a Ship Date table as a calculated or reference table) and relate each copy to the appropriate date key in Sales as a one-to-many, single-direction, active relationship. This design keeps the model simple for report authors, works with row-level security, and avoids the need for USERELATIONSHIP calls. Using inactive relationships would force every measure to enable the relationship manually, while many-to-many or bi-directional settings do not resolve the single-active-relationship constraint.

The Column profile feature opens a pane at the bottom of Power Query Editor that combines detailed column statistics (including minimum, maximum, mean, standard deviation, count, null count, and more) with a value-distribution chart. Selecting a column immediately shows these metrics, making it ideal for identifying outliers. Column distribution only shows a small histogram plus distinct and unique counts, Column quality only shows percentages of Valid/Error/Empty values, and Query diagnostics is unrelated to profiling columns. Therefore, Column profile is the correct choice.