Prepare the data

You import a Product dimension table into Power Query. The source has no reliable unique identifier because the natural business key (ProductCode) contains occasional duplicates caused by legacy data entry, and you must relate this dimension to a large Sales fact table. You want a guaranteed unique key to use as the one-side of the relationship without altering the existing ProductCode values. What is the most appropriate step to take in Power Query?

You are combining two annual sales extracts in Power Query. The 2023 file uses a column named 'CustomerID', while the 2024 file names the same column 'Customer_ID'. All other columns share identical names. After using Append Queries to stack the two tables, you notice the combined table contains two separate columns—'CustomerID' and 'Customer_ID'—each populated for only one year and null for the other. What is the most appropriate way to correct this before loading?

You inherited a Power BI Desktop report that connects to an on-premises SQL Server. After the previous analyst left, the report fails to refresh with an authentication error because it still uses their stored Windows credentials. You need to update the connection to use a service account without rebuilding any queries. In Power BI Desktop, which action should you take?

You are building a Power BI model with a large fact table (Sales) that stays in DirectQuery mode against an Azure SQL Database for near-real-time reporting. A small Product dimension table is used both in slicers and in relationships with the DirectQuery fact table. Slicer interactions are slow because every selection sends a query to the source. You want fast slicer responses while still supporting efficient joins to the DirectQuery fact table. Which storage mode should you set on the Product table?

You are building a Power BI report for a retail company. The fact data resides in a Microsoft Fabric lakehouse as Delta tables and totals over 500 million rows that are refreshed several times per day. Business users require near real-time reporting and interactive report performance similar to Import mode, but you must avoid duplicating the large dataset into the semantic model. Which storage mode should you use for the semantic model?

You are building a Power BI report for a manufacturing operations team that monitors a production database. The team requires that report visuals reflect changes in the source SQL Server database within seconds, and the dataset is far too large to fit into the model's memory. The organization does not use Microsoft Fabric. Which storage mode should you choose when connecting to the data?

You are building a report for the finance department from a SQL Server table containing 500,000 rows of historical monthly ledger entries. The data is updated only once per month during a scheduled batch job. Report users complain that similar reports connected live to the database render slowly, and there is no requirement for real-time data. Which storage mode should you choose to give users the fastest possible query and visual performance?

You import a CSV file of product records into Power Query. A column named 'ProductCode' contains mostly numeric values in the first several hundred rows, but later rows include codes like 'X1050' and 'AB22'. Power Query automatically applied a Whole Number data type, and after loading you notice many ProductCode values appear as errors. What is the best way to prevent these import errors?

You are profiling a 'ProductPrice' column in Power Query Editor. A stakeholder reports that a few products seem to have unusually high prices. You want to quickly see the exact minimum, maximum, and average values for the column, along with the count of distinct and unique values, without writing any code. Which Data Preview feature should you enable to display these summary statistics?

In Power Query Editor, you have a Customers table with an Email column containing values like "jsmith@contoso.com" and "mlee@fabrikam.net". You need to create a new column that contains only the domain portion (e.g., "contoso.com") for grouping customers by company. You are not confident writing the extraction expression manually. Which Power Query feature lets you build this transform by typing sample output values so the engine infers the M code?

You are cleaning a 2-million-row sales table in Power Query Editor. You enable the Column quality and Column distribution features to check for data issues, but you notice the statistics seem to reflect only a small subset of rows. You need the profiling results to reflect all rows in the source before you finalize your cleaning steps. What should you do?

You are cleaning a 4-million-row sales table in Power Query Editor. In the status bar you enabled 'Column quality' and 'Column profile', but a colleague warns you that the error and empty percentages you see may not reflect the true state of the whole table. By default, what is the profiling based on, and what single action lets you validate the metrics against every row?

You are cleaning a sales table in Power Query. After setting the 'UnitPrice' column to a Decimal Number type, the Column Quality pane shows 3% Error and 0% Empty. You need to investigate exactly which source values could not be converted before deciding how to handle them. What should you do first?

You import an employee table into Power Query that contains separate FirstName and LastName columns. The business wants a single FullName column formatted as 'LastName, FirstName' (for example, 'Smith, Anna'), while keeping the original two columns intact for other reports. Which Power Query approach produces the required result with the least manual effort?

You are building a Power BI report in Power Query. Your sales team exports one CSV file per month, each with identical columns (Date, ProductID, Quantity, Amount). You have 24 separate query connections, one per file, and need a single Sales fact table containing all rows from every month. Which transformation should you use to combine these queries most efficiently?

You are preparing a sales table in Power Query. Business users need a text column called 'Deal Size' that displays 'Large' when the OrderAmount is 10000 or greater, 'Medium' when it is between 1000 and 9999.99, and 'Small' otherwise. You want to build this using the Power Query Editor UI with the least amount of custom code. Which feature should you use?

You are building a Power BI report for a sales team that stores its CRM data in Microsoft Dataverse. The team requires that report data refresh on a schedule and that queries perform efficiently against many related tables. You want to use the most appropriate connectivity option in Power BI Desktop. Which approach should you use to connect to the data?

Your organization has a certified shared semantic model in the Power BI service that contains cleaned sales data, standardized measures, and a defined date dimension. A new analyst needs to build several additional reports using this same data and must ensure the reports always reflect the single governed version of the model without duplicating the data or the business logic. What should the analyst do in Power BI Desktop?

You are cleaning a customer dataset in Power Query. Before building relationships, you want to verify that the CustomerID column contains only unique values and identify how many distinct and duplicate values exist. Which column profiling feature in the Power Query Editor should you enable to see this information at a glance?

You import a supplier delivery table into Power Query. Before building the model, you want a quick per-column indicator that shows what percentage of rows in the preview are valid, contain errors, or are empty, so you can decide which columns need cleaning first. Which Power Query feature should you enable in the View ribbon to see this at a glance?