Step2Study

Microsoft Certified: Power BI Data Analyst Associate practice questions

204 free questions with answers and explanations.

Practice test
  1. 1.A data analyst is working on a Power BI model that contains sales data. The 'Sales' table has a 'SalesDate' column and a 'SalesAmount' column. The analyst needs to create a measure that calculates the 'Sales Amount for the Last 30 Days' based on the latest date present in the 'SalesDate' column, regardless of any external date filters applied to the report. Which DAX expression correctly achieves this requirement?Model the data
  2. 2.A data analyst is importing a dataset into Power BI from a SQL Server database. The database contains a table named `SalesOrders` with millions of rows. To improve initial load performance and reduce memory consumption in Power BI Desktop, the analyst wants to retrieve only the data for the current year. Which Power Query transformation should the analyst use to filter the data at the source before loading it into Power BI?Prepare the data
  3. 3.A data modeler is optimizing a Power BI model. The model contains a 'Sales' table with 'SaleAmount' and 'DateKey'. There is also a 'Date' dimension table with 'DateKey', 'CalendarYear', and 'MonthName'. The relationship between 'Sales' and 'Date' is active. The modeler needs to calculate the total sales for the previous year. Which DAX function is specifically designed for this type of time intelligence calculation?Model the data
  4. 4.A data modeler is optimizing a Power BI model for a large e-commerce platform. The model contains a 'Sales' fact table with millions of rows and a 'Customers' dimension table. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Sales[CustomerID]'. The modeler observes that queries involving filtering by customer demographics are slow. Upon inspection, the 'Customers' table contains several columns with very high cardinality (e.g., 'EmailAddress', 'PhoneNumber') that are rarely used for direct filtering in reports but are included in the table. What is the most effective strategy to improve query performance related to customer demographics?Model the data
  5. 5.A Power BI developer is working on a sales report. They have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SaleDate' column, and the 'Date' table has a 'Date' column, which is marked as a date table. A one-to-many relationship exists from 'Date[Date]' to 'Sales[SaleDate]'. The developer needs to calculate the 'Total Sales' for the current year, but the 'SaleDate' column sometimes contains future dates due to pre-orders. The 'Current Date' is defined by the latest date available in the 'Date' table, not the system date. Which DAX expression correctly calculates the 'Total Sales' for the current year based on the latest date in the 'Date' table?Model the data
  6. 6.A data analyst is preparing a dataset in Power Query where a column named 'ProductCost' contains numeric values representing currency. Upon inspection, some entries are found to be 'N/A' or empty strings, which are causing errors when attempting to convert the column to a decimal number type. The business requirement is to treat these non-numeric entries as zero for calculation purposes. Which Power Query transformation sequence will best handle this scenario?Prepare the data
  7. 7.A data analyst is importing data from a CSV file into Power BI. The CSV file contains a column named `EmployeeID` with values like 'EMP-001', 'EMP-002', 'EMP-003'. The analyst needs to ensure that only the numeric part of the ID (e.g., '001', '002', '003') is loaded into the data model and stored as an integer. Which Power Query transformation should be used to achieve this?Prepare the data
  8. 8.A data analyst is working with a Power BI model that includes a 'Sales' table and a 'Products' table. The 'Sales' table contains 'ProductID' and 'OrderDate' columns, while the 'Products' table contains 'ProductID' and 'ProductName' columns. There is a one-to-many relationship from 'Products[ProductID]' to 'Sales[ProductID]'. The analyst needs to create a measure that calculates the total sales amount for a specific product, 'Laptop A', across all sales. Which of the following DAX expressions should the analyst use?Model the data
  9. 9.A data modeler is building a Power BI model for an HR department. The model includes an 'Employees' table with 'EmployeeID', 'ManagerID', and 'Department'. The 'ManagerID' column refers to 'EmployeeID' within the same table, creating a parent-child hierarchy. The HR department needs to calculate the total number of employees reporting to each manager, including indirect reports (employees reporting to subordinates of a manager). Which DAX function is specifically designed to work with parent-child hierarchies to achieve this kind of calculation?Model the data
  10. 10.A data analyst is working with a large transactional dataset in Power Query. The 'TransactionAmount' column is currently of type Text and contains values like '€1,234.56', '$789.00', and '23.45'. The analyst needs to convert this column to a Decimal Number type for calculations. The challenge is that the currency symbols and comma thousands separators vary and must be removed, and the decimal separator is always a period. Which Power Query transformation, considering locale settings, is the most robust way to achieve this conversion?Prepare the data
  11. 11.A data analyst is importing sales transaction data into Power BI from an on-premises SQL Server database. The sales data needs to be refreshed daily, but the dataset is very large (over 500 million rows), and users require near real-time analytics for the most recent month's data. Historical data (older than one month) is less critical and can tolerate slightly older refresh cycles. Which data connectivity mode is MOST appropriate for this scenario?Prepare the data
  12. 12.A data analyst is building a Power BI model for a subscription service to track active subscriptions. The 'Subscriptions' table contains 'SubscriptionID', 'StartDate', and 'EndDate' columns. The analyst needs to calculate the number of active subscriptions at the end of each month. Which DAX function is most appropriate for a measure that counts active subscriptions at a specific point in time?Model the data
  13. 13.A financial analyst is preparing a dataset in Power Query where a column named 'Transaction_Value' is currently formatted as text, with some values containing currency symbols (e.g., '$1,234.56', '€500.00', '1.000,00'). Before performing any calculations, the analyst needs to convert this column to a numeric data type, ensuring that all currency symbols and thousand separators are handled correctly, regardless of their specific type or locale. Which sequence of Power Query transformations is most effective?Prepare the data
  14. 14.A data analyst is preparing a dataset in Power Query where a column named `ProductCategory` contains values like 'Electronics ', ' Clothing', 'Books'. Due to inconsistent data entry, some values have leading or trailing whitespace. The analyst needs to clean this column to ensure all values are standardized (e.g., 'Electronics', 'Clothing', 'Books') before loading into the data model. Which Power Query transformation should be applied?Prepare the data
  15. 15.A data analyst is connecting to an on-premises SQL Server database to retrieve sales data. The database is secured, and the analyst needs to provide specific credentials (username and password) to access the data. Which Power Query authentication method should the analyst choose to connect to this database?Prepare the data
  16. 16.A data modeler is optimizing a Power BI model. The 'Sales' table has millions of rows and includes columns like 'OrderID', 'ProductID', 'CustomerID', and 'SalesAmount'. The 'ProductID' column is an integer. The modeler notices that many visuals use 'ProductID' directly as a filter or in row/column headers, even though a 'Products' dimension table exists with 'ProductID' and 'ProductName'. The 'Products' table has a one-to-many relationship to the 'Sales' table. Which optimization technique should the modeler apply to improve performance and user experience?Model the data
  17. 17.A data analyst is designing a Power BI data model for a global retail company. The model needs to support complex financial reporting, including the calculation of 'Gross Profit' which is defined as 'Sales Amount' minus 'Cost of Goods Sold'. Both 'Sales Amount' and 'Cost of Goods Sold' are measures already defined in the model. The analyst wants to ensure that the 'Gross Profit' calculation is dynamic and responds correctly to all filters applied to the report. Which of the following DAX expressions should the analyst use to calculate 'Gross Profit'?Model the data
  18. 18.A data analyst has a table in Power Query with a 'ProductCategory' column containing values like 'Electronics', 'Clothing ', ' Home Goods', 'Books'. The column has inconsistent leading and trailing spaces. Before loading the data into the model, the analyst needs to remove these extra spaces to ensure consistent categorization. Which Power Query transformation should be applied?Prepare the data
  19. 19.You are connecting Power BI to an Azure SQL Database. The database contains sensitive customer information, and you have been granted read-only access to specific tables. To ensure data privacy and compliance, you need to use a secure connection method that encrypts data in transit. Which data connectivity mode in Power BI Desktop should you choose to meet these requirements while allowing for scheduled refreshes?Prepare the data
  20. 20.You are building a Power BI report for a sales team. You need to combine data from an Excel workbook containing monthly sales figures and a SQL Server database table that stores customer demographics. Both sources have a common column, `CustomerID`, which uniquely identifies each customer. You want to ensure that all sales records are included, even if there is no corresponding customer demographic information. Which Power Query join kind should you use?Prepare the data
  21. 21.A data modeler is building a Power BI model for a global sales organization. The model contains a 'Sales' table with 'OrderID', 'OrderTotalLocalCurrency', and 'CurrencyID'. A 'Currency Exchange Rate' table contains 'CurrencyID', 'ExchangeRate', and 'Date'. The modeler needs to convert 'OrderTotalLocalCurrency' to USD based on the 'Date' of the order. Which DAX pattern enables this row-level currency conversion?Model the data
  22. 22.A data modeler is creating a Power BI report that analyzes customer activity over time. The 'Customers' table contains 'CustomerID' and 'SignupDate'. The 'Activity' table contains 'ActivityID', 'CustomerID', and 'ActivityDate'. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Activity[CustomerID]'. The modeler needs to calculate the number of active customers at the end of each month. An 'Active Customer' is defined as any customer who has had at least one activity within the last 90 days from the end of the current month. Which DAX expression should be used?Model the data
  23. 23.A data analyst is importing data from an Excel workbook into Power BI. The workbook contains three sheets: 'Sales2022', 'Sales2023', and 'ProductCatalog'. The analyst needs to combine the sales data from 'Sales2022' and 'Sales2023' into a single table and also load 'ProductCatalog' as a separate table. The 'Sales2022' and 'Sales2023' sheets have identical structures. Which Power Query operation is the most efficient way to achieve this for the sales data?Prepare the data
  24. 24.A data modeler is optimizing a Power BI model for a large enterprise. The model contains a 'Sales' fact table and several dimension tables. The 'Sales' table has a 'DateKey' column which is an integer representing the date (e.g., 20230101). This 'DateKey' is related to the 'Date' dimension table's 'DateKey' column. The modeler notices that many DAX calculations involving dates are performing slowly, especially when filtering across long date ranges. What is a common optimization strategy for date dimensions in such scenarios?Model the data
  25. 25.A Power BI developer is importing data from a customer relationship management (CRM) system. The data contains a column named 'CustomerStatus' with values like 'Active', 'Inactive', 'Pending', and 'ACTIVE'. The developer needs to ensure that 'Active' and 'ACTIVE' are treated as the same category for reporting purposes. Which Power Query transformation should be applied to standardize this column?Prepare the data
  26. 26.A data modeler is preparing a fact table in Power Query from a transactional system. The raw data contains a column named 'TransactionDateTime' with values like '2023-05-10T14:30:00Z'. For analytical purposes, the modeler needs separate columns for 'TransactionDate' (e.g., '2023-05-10') and 'TransactionTime' (e.g., '14:30:00'). Which Power Query transformation simplifies this extraction process?Prepare the data
  27. 27.A data analyst is building a Power BI model for a global company. They have a 'Sales' table and a 'Products' table. The 'Products' table contains 'ProductID', 'ProductName', and 'Category'. The 'Sales' table contains 'ProductID', 'SaleAmount', and 'Region'. The analyst needs to create a measure that calculates the total sales for 'Electronics' category products, regardless of any other filters applied to the 'Products' table. Which DAX expression achieves this?Model the data
  28. 28.A data modeler is consolidating sales data from multiple regional databases into a single Power BI model. Each regional database uses a different unique identifier for products, but a master 'Product Mapping' table exists that links all regional product IDs to a single global 'MasterProductID'. The model needs to analyze sales by 'MasterProductID' and include region-specific product names. Which data modeling approach is best for handling these varied product IDs and integrating them into a unified model for analysis?Model the data
  29. 29.A data modeler is optimizing a Power BI model for a financial institution. The model contains a 'Transactions' fact table and a 'Accounts' dimension table. The 'Transactions' table has a 'DebitAccountID' and a 'CreditAccountID', both related to 'Accounts[AccountID]'. The modeler wants to create measures that can dynamically filter transactions based on whether an account was involved as a debit or a credit. Which DAX function is essential for enabling multiple active relationships between 'Transactions' and 'Accounts' in measures?Model the data
  30. 30.A data analyst is working with a sales dataset in Power Query. The 'SalesAmount' column is currently of type 'Text' and contains values like '1,234.56', '500.00', and occasionally some non-numeric entries like 'N/A' or blank cells. The analyst needs to convert this column to a 'Decimal Number' data type, ensuring that valid numbers are converted correctly and invalid entries are handled gracefully without causing an entire query failure. Which Power Query transformation strategy offers the most robust conversion?Prepare the data
  31. 31.A data analyst is building a Power BI model for a global sales company. The model includes a 'Sales' table and a 'Currency Exchange Rates' table. The 'Sales' table records sales amounts in various local currencies, and the 'Currency Exchange Rates' table provides daily exchange rates to a common reporting currency (USD). The analyst needs to calculate the total sales in USD for each transaction. Which DAX function is most appropriate for a row-level currency conversion within a measure?Model the data
  32. 32.A data analyst is performing data profiling in Power Query on a customer dataset. The 'Email' column is critical for uniqueness, but upon using 'Column profile' and 'Column quality' features, it's observed that there are several blank entries and some entries marked as 'Error'. The analyst needs to understand the exact count of these problematic entries to assess data quality. Which Power Query profiling metric provides this information directly?Prepare the data
  33. 33.You are working with a dataset in Power Query where a column named 'Product_Code' contains values like 'P-12345', 'A-67890', 'B-11223'. You need to extract only the numeric part (e.g., '12345', '67890', '11223') from this column. The prefix (e.g., 'P-', 'A-', 'B-') can vary in length but always ends with a hyphen. Which Power Query transformation step is the most appropriate to achieve this?Prepare the data
  34. 34.A data engineer is integrating data from a legacy system into Power BI. The system exports sales data in a fixed-width text file where each field occupies a specific number of characters, without delimiters. For example, a row might look like '20240101CUST001 PRODXYZ1234.56'. The engineer needs to extract '20240101' (Date), 'CUST001' (CustomerID), 'PRODXYZ' (ProductID), and '1234.56' (Amount) into separate columns. Which Power Query transformation is most suitable for this task?Prepare the data
  35. 35.A data modeler is optimizing a Power BI model for an inventory management system. The model has a 'InventoryTransactions' fact table and a 'Products' dimension table. To improve query performance, the modeler wants to reduce the number of distinct values in certain columns within the 'InventoryTransactions' table without losing critical information. For example, a 'TransactionDescription' column has many unique text values but often describes similar types of transactions. What is the most effective data modeling technique to address this specific issue?Model the data
  36. 36.A Power BI developer is importing data from a folder containing multiple CSV files, each representing sales data for a different region. All CSV files have the same structure and column headers. The developer needs to combine all these files into a single table in Power Query. Which option in the 'Get Data' interface is specifically designed for this scenario?Prepare the data
  37. 37.A data modeler is preparing a fact table in Power Query from a transactional system. The table contains a 'SalesDateTime' column. For reporting purposes, the modeler needs to create a separate 'Date' column containing only the date part (without time) and a 'Time' column containing only the time part (without date). These new columns should be derived from 'SalesDateTime' and added to the table. Which Power Query transformation approach is most suitable?Prepare the data
  38. 38.A data modeler is optimizing a Power BI model for a large manufacturing company. The model includes a 'ProductionLog' fact table with billions of rows, containing granular event data. This table has a 'Timestamp' column (datetime) and several high-cardinality text columns used for detailed logging. The model is experiencing slow refresh times and report performance. Which optimization technique specifically targets improving performance related to high-cardinality text columns in a fact table?Model the data
  39. 39.A data modeler is working on a Power BI model that contains a 'Customers' table, a 'Products' table, and a 'Sales' fact table. The 'Sales' table has millions of rows. The modeler needs to create a measure to calculate the count of distinct customers who purchased a specific product, 'Product Z'. The report is experiencing slow performance when this measure is used. Which DAX expression would be most efficient for this calculation?Model the data
  40. 40.A data analyst is combining two tables in Power Query: 'Sales' (containing daily sales transactions) and 'Products' (containing product details like category and price). Both tables have a 'ProductID' column. The requirement is to include all sales transactions, even those with a 'ProductID' that does not exist in the 'Products' table, and to show product details where they are available. Which type of join should be used?Prepare the data
  41. 41.A data analyst is creating a Power BI report to track product sales. The model includes a 'Sales' table and a 'Products' table. The 'Products' table has a 'ProductCategory' column. The analyst needs to create a measure that calculates the sales amount for a specific product category, say 'Electronics', and always displays this value regardless of any filters applied to the 'Products' table in the report. Which DAX expression achieves this?Model the data
  42. 42.A data analyst is integrating data from a legacy system into Power BI. The system exports transaction data into a series of CSV files, one for each month. Each CSV file has the same schema, and contains columns like 'TransactionID', 'TransactionDate', and 'Amount'. The analyst needs to combine all these monthly CSV files into a single table in Power Query. Which Power Query connector and subsequent action are most appropriate for this task?Prepare the data
  43. 43.A data modeler is optimizing a Power BI model with a complex star schema. The 'Sales' fact table has millions of rows and is related to several dimension tables including 'Products', 'Customers', and 'Date'. To improve query performance, the modeler wants to reduce the cardinality where possible. Which of the following columns, if present in the 'Sales' fact table, would be the most impactful to remove or move to a dimension table for performance optimization?Model the data
  44. 44.A data modeler is designing a Power BI model for a retail chain. The model includes a 'Sales' table (Fact) and a 'Products' table (Dimension). The 'Products' table contains a 'CategoryID' column, and the 'Sales' table contains a 'ProductID' column. There is a one-to-many relationship between 'Products[ProductID]' and 'Sales[ProductID]'. The modeler needs to calculate the total sales for a specific category, regardless of any other filters applied to the 'Products' table, except for the category filter itself. Which DAX function should be used to achieve this?Model the data
  45. 45.A data modeler is optimizing a Power BI model with a large 'Sales' fact table and several dimension tables. The model includes a 'Geography' dimension table with 'Country', 'State', and 'City' columns. The modeler observes that queries involving 'City' are significantly slower than those involving 'Country' or 'State'. The 'City' column has a very high number of unique values. Which modeling technique is most likely to improve query performance related to the 'City' column without losing analytical capability?Model the data
  46. 46.A data modeler is integrating data from multiple regional databases into a single Power BI model. The regional databases have slightly different column names for the same customer identifier (e.g., 'CustID', 'CustomerID', 'Client_ID'). To ensure a consistent and unified customer dimension table, what is the most effective ETL step the modeler should perform?Model the data
  47. 47.A data modeler is optimizing a Power BI model for a large enterprise. The 'Sales' fact table has millions of rows and is related to several dimension tables. The model is experiencing slow query performance. Upon inspection, the modeler notices that the 'Sales' table has a 'ProductDescription' column which is a text column with high cardinality and is not used for filtering or grouping in reports. What action should the modeler take to improve model performance?Model the data
  48. 48.A data analyst is preparing a dataset in Power Query for a sales report. The dataset includes a 'ProductCode' column that contains unique identifiers for products. The analyst notices that some product codes are entered with inconsistent casing (e.g., 'ABC123', 'abc123', 'Abc123'). To ensure that these are treated as the same product for analysis, the analyst needs to standardize the casing of this column. Which Power Query transformation should be applied?Prepare the data
  49. 49.A Power BI developer needs to load data from an Excel workbook that contains multiple sheets. Some sheets (e.g., 'Sales_Q1', 'Sales_Q2') contain transactional data that needs to be combined, while other sheets (e.g., 'Lookup_Products', 'Settings') contain lookup tables or configuration data that should be loaded separately. When using the Excel workbook connector, how can the developer efficiently select and manage these different types of sheets?Prepare the data
  50. 50.A data analyst is working with a large sales dataset in Power Query. The dataset includes a 'TransactionID' column, which is a unique identifier. To improve query performance and reduce the data model size, the analyst wants to ensure that this column is stored as the smallest possible whole number type, given that `TransactionID` values range from 1 to 1,500,000. Which Power Query transformation should be used to achieve this and what is the most appropriate data type?Prepare the data