Download Workbooks Data analyst Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. Which Power Query operation would you use to convert a column of text dates like “2023‑ 04 ‑ 01 ” into proper date data type? A) Split Column B) Change Type → Date C) Replace Values D) Group By Answer: B Explanation: The “Change Type” option lets you convert a column’s data type; selecting Date transforms text dates into true date values. Question 2. In a relational model, which table typically contains the measurable numerical data such as sales amount? A) Dimension table B) Fact table C) Lookup table D) Bridge table Answer: B Explanation: Fact tables store quantitative metrics (e.g., sales, quantity) that can be aggregated, while dimension tables hold descriptive attributes. Question 3. When connecting to a large Azure SQL Database in Power BI, which storage mode helps keep the dataset size small on the client side? A) Import B) DirectQuery C) Live Connection D) Composite Answer: B
Explanation: DirectQuery leaves the data in the source and queries it in real time, avoiding a full import that would increase client‑side storage. Question 4. Which DAX function is used to modify the filter context of a calculation? A) SUMX B) CALCULATE C) FILTER D) ALLSELECTED Answer: B Explanation: CALCULATE evaluates an expression in a modified filter context, allowing you to add, remove, or replace filters. Question 5. In Excel, which function would you use to find the most frequently occurring value in a range? A) MODE.SNGL B) MEDIAN C) AVERAGE D) COUNTIF Answer: A Explanation: MODE.SNGL returns the single most common value (the mode) in a dataset. Question 6. Which of the following best describes a many‑to‑many relationship in a data model? A) Each row in Table A matches exactly one row in Table B B) One row in Table A can match multiple rows in Table B and vice‑versa C) Table A and Table B have no matching rows D) Table A is a lookup table for Table B
B) The fact table contains foreign keys pointing to each dimension table C) Dimension tables store aggregated metrics D) Fact tables are normalized to third normal form Answer: B Explanation: The fact table holds foreign keys that reference the primary keys of each dimension table, forming a star pattern. Question 10. Which DAX function would you use to compute a year‑over‑year growth percentage for a measure called Sales? A) SAMEPERIODLASTYEAR B) TOTALYTD C) DIVIDE D) PREVIOUSYEAR Answer: A Explanation: SAMEPERIODLASTYEAR returns the value of a measure for the same period in the previous year, enabling YoY comparisons. Question 11. When merging two queries in Power Query, which join type keeps all rows from the first (left) table and matches rows from the second (right) table where possible? A) Inner B) Full Outer C) Left Outer D) Right Anti Answer: C Explanation: Left Outer join retains every row from the left table and adds matching rows from the right table, inserting nulls when no match exists.
Question 12. In statistical hypothesis testing, what does a p‑value of 0.03 indicate when the significance level is 0.05? A) Fail to reject the null hypothesis B) Accept the alternative hypothesis without testing C) Reject the null hypothesis D) The test is inconclusive Answer: C Explanation: A p‑value lower than α (0.05) suggests the observed result is unlikely under the null, leading to rejection of the null hypothesis. Question 13. Which Excel function can be used to extract the year component from a date stored in cell A2? A) YEAR(A2) B) DATE(YEAR(A2),1,1) C) TEXT(A2,"yyyy") D) ALL of the above Answer: D Explanation: YEAR directly returns the year; DATE with YEAR also yields a date representing the year; TEXT with format “yyyy” returns the year as text. All achieve the same goal. Question 14. What is the primary purpose of row‑level security (RLS) in a Power BI dataset? A) Prevent users from seeing the report layout B) Restrict access to specific rows based on user identity C) Encrypt the data at rest D) Hide column names from viewers Answer: B
D) The slope of the regression line Answer: B Explanation: R‑squared quantifies how much of the variation in the outcome is captured by the predictors. Question 18. Which of the following is an example of a categorical (nominal) variable? A Question 18. Which of the following is an example of a categorical (nominal) variable? A) Age in years B) Sales revenue C) Country code (e.g., US, CA, MX) D) Temperature in Celsius Answer: C Explanation: Country code represents categories without inherent order, making it nominal. Question 19. In Power BI, what does setting the cross‑filter direction to “Both” enable? A) Filters flow only from one side of the relationship B) Filters flow in both directions between related tables C) No filtering occurs at all D) Only row‑level security is applied Answer: B Explanation: “Both” allows filter context to propagate from either table to the other, useful for many‑to‑many scenarios.
Question 20. Which Excel feature allows you to summarize large datasets by creating dynamic groupings and calculations without writing formulas? A) Data Validation B) Power Query C) PivotTable D) Conditional Formatting Answer: C Explanation: PivotTables enable quick aggregation, grouping, and drill‑down of data interactively. Question 21. When cleaning data, you find duplicate rows based on all columns. Which Power Query transformation is most efficient for removing them? A) Remove Columns B) Keep Duplicates C) Remove Duplicates D) Group By Answer: C Explanation: “Remove Duplicates” eliminates rows that are identical across all selected columns. Question 22. In a snowflake schema, dimension tables are: A) Denormalized to a single flat table B) Normalized into multiple related tables C) Not used at all D) Stored as JSON objects Answer: B Explanation: Snowflake schemas normalize dimensions into additional tables to reduce redundancy.
Answer: A Explanation: LASTNONBLANK evaluates an expression and returns the last (most recent) non‑blank result within the current context. Question 26. When you need to calculate a moving average over the last 12 months of sales, which DAX time‑intelligence function is most appropriate? A) TOTALYTD B) DATEADD C) CALCULATE with FILTER and DATESINPERIOD D) SAMEPERIODLASTYEAR Answer: C Explanation: Using CALCULATE with DATESINPERIOD defines a rolling window (e.g., last 12 months) for the moving average. Question 27. Which Excel function can be used to remove leading and trailing spaces from a text string? A) TRIM B) CLEAN C) SUBSTITUTE D) REPLACE Answer: A Explanation: TRIM removes extra spaces before and after the text, leaving single spaces between words. Question 28. In data governance, what is the purpose of a data lineage diagram? A) Show physical storage locations of files B) Visualize the flow and transformation of data from source to destination
C) List all users who have accessed the data D) Encrypt the data at rest Answer: B Explanation: Data lineage tracks how data moves, is transformed, and is stored, providing transparency and auditability. Question 29. Which of the following is a semi‑additive measure in a data warehouse? A) Number of customers (count) B) Daily sales amount (sum) C) Account balance (snapshot) D) Quantity sold (sum) Answer: C Explanation: Snapshots (e.g., account balance) are additive over time but not across other dimensions, making them semi‑additive. Question 30. When applying a filter to a Power BI visual, which DAX function can be used to ignore that visual’s filters and calculate a total across the entire dataset? A) ALLSELECTED B) ALLEXCEPT C) ALL D) REMOVEFILTERS Answer: C Explanation: ALL removes all filter context from the specified column or table, returning the overall total. Question 31. Which Excel feature allows you to enforce that a cell only accepts dates after January 1 2020?
Question 34. When designing a dashboard, which principle helps prevent visual overload? A) Use as many colors as possible B) Show every metric on a single page C) Prioritize key performance indicators and use white space D) Rotate charts every hour automatically Answer: C Explanation: Emphasizing essential KPIs and providing ample white space improves readability and focus. Question 35. Which DAX function would you use to calculate the total sales for the current year up to the latest date in the filter context? A) TOTALYTD(Sales, Dates[Date]) B) TOTALMTD(Sales, Dates[Date]) C) TOTALQTD(Sales, Dates[Date]) D) CALCULATE(Sales, YEAR(Dates[Date]) = YEAR(TODAY())) Answer: A Explanation: TOTALYTD aggregates values from the start of the year to the latest date in the current filter context. Question 36. In Excel, what does the “#VALUE!” error typically indicate? A) Division by zero B) Invalid data type for the operation C) Reference to a non‑existent cell D) Circular reference Answer: B
Explanation: #VALUE! appears when a formula receives an argument of the wrong type (e.g., adding text to a number). Question 37. Which of the following is a characteristic of a “bronze” layer in the Medallion Architecture? A) Highly curated, business‑ready data B) Raw, ingested data with minimal transformation C) Aggregated metrics for reporting D) Data encrypted with production keys Answer: B Explanation: The bronze layer stores raw source data, preserving its original format before cleaning or enrichment. Question 38. When performing clustering on customer data, which algorithm is most commonly used for partitioning into a predefined number of groups? A) Decision Tree B) K‑Means C) Linear Regression D) Apriori Answer: B Explanation: K‑Means clusters data into K groups by minimizing intra‑cluster variance. Question 39. In Power BI, which visual property allows you to display data labels only for bars that exceed a certain value? A) Data colors B) Conditional formatting → Data label rules C) Tooltip
B) Inventory on hand (last non‑blank) C) Number of orders (count) D) Average discount (average) Answer: B Explanation: Inventory on hand is a snapshot measure; the “last non‑blank” aggregation is needed to reflect the most recent quantity. Question 43. When creating a calculated column in Power BI, the expression is evaluated: A) At query time, based on the current filter context B) Once per row during data refresh, stored in the model C) Only when the visual is rendered D) Dynamically for each user session Answer: B Explanation: Calculated columns are computed during data load and stored as part of the model, not recomputed on every query. Question 44. Which statistical distribution is symmetric and defined by its mean and standard deviation? A) Poisson B) Binomial C) Normal D) Exponential Answer: C Explanation: The normal distribution is symmetric around its mean and fully described by mean and standard deviation.
Question 45. In Power BI, what is the effect of setting a relationship’s cardinality to “One‑to‑One”? A) Both tables must have unique values in the related columns B) One table can have duplicate values, the other cannot C) It forces a many‑to‑many relationship D) It disables cross‑filtering Answer: A Explanation: One‑to‑One requires each side of the relationship to contain unique keys, ensuring a strict 1:1 mapping. Question 46. Which Excel function returns the most recent date in a range, ignoring blanks? A) MAX B) LARGE C) DATEVALUE D) LOOKUP Answer: A Explanation: MAX returns the highest (most recent) numeric value, which works for dates because they are stored as serial numbers. Question 47. When performing a hypothesis test for a proportion, which statistic is typically used? A) t‑statistic B) z‑statistic C) F‑statistic D) chi‑square statistic Answer: B Explanation: Proportion tests often use the z‑statistic because the sampling distribution of a proportion approximates normal under large samples.
Answer: B Explanation: The Pareto Principle (80/20 rule) is used to focus on the most impactful factors. Question 51. Which DAX function can be used to create a dynamic ranking of products based on sales within the current filter context? A) RANKX B) TOPN C) ORDERBY D) SORTBYCOLUMN Answer: A Explanation: RANKX evaluates an expression and returns the rank of each row relative to others in the current context. Question 52. When applying data masking for sensitive columns, which technique replaces characters with a generic symbol? A) Encryption B) Tokenization C) Redaction D) Hashing Answer: C Explanation: Redaction masks data by substituting characters (e.g., *****), making the original value unreadable while preserving format. Question 53. In Excel, which function can be used to count the number of cells that meet multiple criteria? A) COUNTIF B) COUNTIFS
C) SUMPRODUCT
D) FILTER
Answer: B Explanation: COUNTIFS allows multiple condition ranges, counting cells that satisfy all specified criteria. Question 54. Which of the following is NOT a typical step in the ETL process? A) Extracting data from source systems B) Transforming data to meet business rules C) Loading data into a target repository D) Visualizing data in a dashboard Answer: D Explanation: Visualization is part of reporting/analysis, not a core ETL step. Question 55. In Power BI, what does the “Drillthrough” feature enable? A) Changing the visual type on the fly B) Navigating to a different report page filtered to the selected data point C) Exporting data to Excel automatically D) Applying a global filter to all pages Answer: B Explanation: Drillthrough creates a page that shows detailed information for a specific value selected in another visual. Question 56. Which statistical test is appropriate for assessing the association between two categorical variables? A) t‑test
