Practice Test #1

To create one Products column from both tables, you must first load both Excel tables into Power Query. Next, use Append because append stacks rows from tables with the same schema; Merge is incorrect because it performs a join and is used to add columns based on matching keys. After appending, remove duplicates on the combined query so repeated values such as abc and mno appear only once. Removing errors is irrelevant because the scenario does not mention any errors, and removing duplicates before appending would not eliminate duplicates that exist across both tables.

Yes. SalesRowID and AuditID are technical/operational columns that do not help identify products bought together. Basket analysis requires grouping lines into a transaction (SalesOrderNumber) and identifying the products in that transaction (ProductKey), then optionally slicing by territory/date/customer. SalesRowID is a source-system row identifier (unique combination of SalesOrderNumber and SalesOrderLineNumber). Since SalesOrderNumber and SalesOrderLineNumber already exist, SalesRowID is redundant for analysis and not needed for relationships. AuditID tracks the ETL/load process that last updated the row; it is useful for data engineering troubleshooting but not for analytical grouping, filtering, or measures related to co-purchase behavior. Removing both columns reduces model size and can improve refresh/query performance without impeding the stated analysis goals.

No. Both OrderDateKey and OrderDate are not necessary for basket analysis. In a star schema, OrderDateKey is typically used to relate the Sales fact table to a Date dimension (which then provides year/quarter/month/day attributes for slicing). For basket analysis across time, that relationship is sufficient. The OrderDate (datetime) column is often redundant if you already have a proper Date dimension and do not need time-of-day granularity. Basket analysis is usually performed at the transaction level (SalesOrderNumber) and may be filtered by date, but that can be done via the Date dimension using OrderDateKey. You would keep OrderDate only if you specifically need the timestamp (hours/minutes) or if you lack a Date dimension/relationship. Given the prompt states related dimension tables are imported, OrderDateKey alone is enough.

No. TaxAmt does not need to retain the current number of decimal places to perform basket analysis. Basket analysis primarily evaluates which products co-occur in the same order; it relies on transaction identifiers and product identifiers, and sometimes quantities. Tax amount precision is not part of determining whether two products were bought together. Even if you were to include value-based metrics (e.g., total basket value), you could typically round currency values to a sensible precision (often 2 decimal places) without changing the co-occurrence results. In many basket-analysis models, TaxAmt is not required at all and could be removed to optimize the model. Therefore, retaining the exact current decimal precision is not a requirement for achieving the stated analysis goal.

Answer A (Pass) is appropriate because the correct sequence of actions is clear from Power BI Folder connector behavior. Correct sequence (three actions): 1) From Power BI Desktop, select Get Data, and then select Folder. 2) From Power Query Editor, remove the Content column. 3) From Power Query Editor, expand the Attributes column. Why: The Folder connector produces a file listing; Content is the binary file payload. Removing Content ensures the dataset does not ingest/store CSV data. Expanding Attributes exposes additional metadata (for example, size, hidden, read-only flags) as columns, creating a single metadata dataset across all 100 files. Why others are wrong: Get Data > Text/CSV would connect to a single CSV, not a folder. Combining the Content column would read and append the CSV contents, violating the “must NOT store the data” requirement. Removing Attributes would discard useful metadata rather than exposing it.

Correct answer: D (75 rows). Because UnitsInStock has “Summarize by” set to None (Don’t summarize), adding it to a table visual will display the detail-level values rather than an aggregation. With no other grouping fields in the visual, Power BI will list the column’s values per underlying row in the model. The prompt states there are 75 non-null values, so the table will show 75 rows (nulls would not appear as separate numeric values in this context). Why the others are wrong: - A (0 rows): There is data, so the visual will render rows. - B (1 row): You’d typically get 1 row only if the field were aggregated (e.g., Sum) and it was the only field in the visual. - C (51 rows): 51 is the count of unique values, but a table visual does not automatically de-duplicate a non-aggregated column; it shows row-level records, not distinct values, unless you explicitly use a distinct-count/summary approach.

Correct answer: B (reduce). Changing the column’s “Summarize by” setting from None to an aggregation (such as Sum, Average, Min, Max, Count) changes the default behavior when the field is added to a visual. If you then add UnitsInStock (by itself) to a table visual, Power BI will return a single aggregated value for the current filter context, which typically results in 1 row instead of many detail rows. Therefore, the number of rows is reduced. Why the others are wrong: - A (maintain): The row count would only be maintained if the field remained non-summarized or if additional grouping columns were added that force detail rows. - C (increase): Aggregation collapses detail into fewer results; it does not create more rows. Even if you used a grouping field, aggregation generally reduces granularity compared to showing raw rows.

No. This visual uses Posts[Title] and Traffic[DateTime] with no filter, and it is explicitly “Top 10 blog posts of all time.” To support this, the model must be able to count/aggregate traffic across the full history of Traffic for blog posts. If you applied a query-level DateTime filter (for example, last 7/30/90 days) to reduce refresh time, you would change the meaning of “all time” and the visual would become incorrect. However, you can still reduce load time by filtering Traffic to only blog-related rows (URL Visited contains “blog”), because the visual is about blog posts, not the entire website. That filter keeps the “all time” requirement intact while reducing rows. So the correct response to whether you can apply a time-based reduction for this visual is No.

No. Although this single visual is filtered to the last seven days, modifying the shared Traffic query to keep only the last seven days would break other visuals that require all-time traffic data. Query changes must preserve the requirements of the whole report, not just one visual. Therefore, a global DateTime filter is not an appropriate refresh optimization in this scenario.

Yes. This visual is “Blog visits over time” and includes a filter Traffic[URL Visited] contains “blog.” Since the report is for the marketing department and focuses on blog traffic, pushing this filter into the Traffic query is a classic refresh optimization: it reduces the number of rows imported from website logs (which are usually very large) and reduces the work needed for relationship matching and compression. From an exam perspective, this is one of the most defensible query changes because it does not change the meaning of any blog-only visual; it simply removes non-blog website traffic that the report does not analyze. It also tends to preserve query folding when the source is a database or log store that can filter server-side.

Yes. This visual only analyzes visits to blog pages that came from external referrers, as indicated by URL Visited contains 'blog' and Referring URL does not start with '/'. Applying these filters in Power Query reduces the number of rows loaded and can improve refresh performance without changing the meaning of this visual. The other visuals shown do not require non-blog traffic or internal referrals, so removing those rows is a valid optimization for this report scenario.

The dashed line labeled “Year Average Employee Count” is created using an average reference line from the Analytics pane. In Power BI line charts, reference lines can be configured as Average, Median, Min, Max, Constant, etc., and they commonly render as a horizontal dashed line with an optional label showing the computed value. This matches the exhibit: a single horizontal dashed line spanning the chart with an average value. Why others are wrong: - A (trend line): a trend line typically slopes (linear regression) and is used to show direction over time, not a constant horizontal average. - B (secondary axis): a secondary axis is used when plotting measures with different scales; it doesn’t inherently create an average line. - D (two measures in the Values bucket): adding a second measure would plot another series (another line) that varies by month, not a constant dashed average across all months unless you specifically engineered a constant measure; the standard feature for this is the Analytics reference line.

To enable drill down to weeks or days, you add the Weeks and Days fields to the Axis bucket (X-axis) to form a hierarchy. Power BI drill-down works by having multiple categorical levels on the axis; users can then use Drill Down/Expand controls to navigate from Month to Week to Day within the same visual. Why others are wrong: - B (Values): Values contains measures (what is being aggregated and plotted), not the categorical levels used for drill navigation. - C (Legend): Legend splits the measure into multiple series (e.g., by department), but it does not create drill levels. - D (Secondary values): this is not the standard mechanism for drill-down; drill-down is driven by axis hierarchies (or sometimes by using the built-in Date hierarchy), not by secondary value wells.