Prepare the data

You are designing a Power BI solution for a retail company. The data includes point-of-sale transactions with columns: TransactionID, StoreID, ProductID, Quantity, SalesAmount, TransactionDate. The company wants to analyze sales by hour of day. What is the best way to prepare the time dimension?

You have a Power BI dataset that uses Import mode and refreshes daily. The source data includes a column 'LastModifiedDate'. You want to reduce the amount of data loaded during each refresh by only loading rows that have changed since the last refresh. Which feature should you configure?

You are transforming a table that contains a 'Date' column in text format (e.g., '2026-01-15'). You need to create separate columns for Year, Month, and Day. Which THREE Power Query transformations can you use? (Choose three.)

Refer to the exhibit. You are reviewing a Power Query script. The script fails with a 'DataSource.Error: Microsoft SQL: Login failed for user' error. Which step should you check first?

You are a data analyst for a retail company. You have a Power BI semantic model that imports data from an on-premises SQL Server database using an on-premises data gateway. The dataset contains a table named Products with a column named Category. You need to ensure that when users open a report, they only see data for the categories they are authorized to view. You have a table named UserCategories that maps user principal names (UPNs) to categories. What should you do?

Which THREE actions in Power Query Editor can improve the performance of data refresh? (Select three.)

You are developing a Power BI semantic model that uses a large fact table from Azure Synapse Analytics. You need to optimize the model for performance. Which THREE actions should you take?

You are creating a Power BI report from a SQL Server database that contains a table Orders with columns: OrderDate, CustomerID, ProductID, Quantity, UnitPrice. You need to build a star schema. Which THREE tables should you create? (Choose three.)

You have a Power BI dataset that uses data from Microsoft Excel files stored in SharePoint Online. Users report that the data is not refreshing as scheduled. You verify that the gateway is installed and running. What is the most likely cause of the refresh failure?

You are importing data from a SQL Server database. The source table has a column 'ModifiedDate' of type datetime2. In Power Query, you want to ensure that only rows modified within the last 7 days are loaded. Which THREE steps should you take?

You have a Power BI semantic model that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. The model is used by a real-time dashboard. Users report that the dashboard is slow. You need to improve query performance without changing the source system. Which action should you take?

You are connecting to an Azure SQL Database from Power BI Desktop. The database contains a view that returns thousands of rows. You only need the last 100 rows for analysis. What is the most efficient way to reduce the data loaded?

You have a Power BI semantic model that uses Import mode with a SQL Server data source. The refresh takes over two hours. You need to reduce the refresh time while keeping data up-to-date. What is the best strategy?

Which TWO are valid reasons to use a dataflow in Power BI when preparing data?

You are a Power BI data analyst at a healthcare organization. You import patient encounter data from an Azure Synapse Analytics dedicated SQL pool. The data contains a column named EncounterDate of type datetime2. You need to create a Power Query transformation that adds a column showing the fiscal year, which starts on July 1. The fiscal year should be labeled as FY2024 for dates from July 1, 2023, through June 30, 2024. Which transformation should you use?

Which THREE factors should you consider when designing an incremental refresh policy for a large fact table in Power BI?

You are importing data from an Excel workbook that contains multiple sheets. You only need data from the 'Sales' sheet. In Power Query Editor, what should you do to load only that sheet?

You are reviewing a Power Query query that loads data from a SQL Server database. The query includes multiple steps that perform data transformation. You want to ensure that the query is optimized by pushing as many transformations as possible to the SQL Server. What should you look for?

Refer to the exhibit. You load the Sales table into Power BI. You need to calculate the total net sales after discount (SalesAmount * (1 - Discount)). However, some rows have Null in the Discount column. What is the correct DAX measure?

You have a table with a column 'FullName' that contains names in the format 'Last, First'. You need to split this column into 'LastName' and 'FirstName' columns. Which Power Query transformation should you use?

You are analyzing a Power BI dataset definition. The dataset refreshes but recently started failing with the error 'The 'OrderDate' column of the table 'Orders' has a date value that is out of range.' You need to diagnose the issue. What is the most likely cause?

You are importing data from an Excel workbook that contains a column 'Region' with values such as 'North', 'South', 'East', 'West', and some blank cells. You need to replace the blank cells with 'Unknown' to ensure consistent reporting. What is the most efficient way to achieve this in Power Query?

You need to combine data from three different SharePoint lists into a single table for analysis. The lists have different column names but contain similar data. What is the best approach in Power Query?

You are a Power BI data analyst at a university. You import a table named Enrollments from an on-premises SQL Server database. The table contains a column named StudentEmail that is stored as text and sometimes has leading or trailing spaces. You need to remove these spaces so that the column can be used to create a relationship with a Students table that also has a StudentEmail column. You must perform this cleanup in Power Query, and the transformation must apply to the entire column without creating a new column. Which transformation should you use?

You are connecting to an Excel workbook stored in Microsoft SharePoint Online. You want to refresh the data in Power BI service without manual intervention. Which type of gateway is required?

You are importing data from a CSV file that contains a column with mixed data types (numbers and text). Power BI automatically assigns the data type as Text. You need to perform numerical aggregations on this column. What should you do?

You are designing a data model in Power BI. You have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SalesDate' column of type Date. You need to create a relationship between the tables, but the 'Date' table contains dates from 2010 to 2025, while the 'Sales' table only has data from 2020. Which type of relationship should you create to ensure optimal performance and correct filtering?

You are reviewing a DAX query in DAX Studio. The exhibit shows a query that returns a table. What is the purpose of the SUMMARIZE function in this query?

You are using Power Query to combine data from multiple CSV files in a folder. Each file has the same structure. You want to append all rows into a single table. Which Power Query function should you use?

Refer to the exhibit. The Power Query M code connects to a SQL Server database and performs data transformation. However, the query is failing with a privacy level error. What is the most likely cause?

You are preparing a data model that uses a date table. You need to ensure that the date table includes all dates from January 1, 2020 to December 31, 2025. What is the most efficient way to create this date table in Power Query?

Which THREE of the following are best practices for data preparation in Power BI to improve performance and maintainability? (Select THREE.)

You are developing a Power BI semantic model that must combine data from an on-premises SQL Server database and a SharePoint Online list. The organization requires that credentials for the on-premises data source be stored securely and not shared with users. Which data connectivity approach should you use?

You have a Power Query query that loads data from an OData source. You need to reduce the amount of data loaded into the data model. What is the best practice?

You are working with a Power BI dataset that imports data from a web API. The API returns a JSON response containing a nested array of order details for each order. You need to flatten this nested array so that each order detail becomes a separate row in the table. Which Power Query transformation should you use?

You are profiling data in Power Query Editor. Which THREE tasks can you perform using the Column Profile feature?

You are connecting to a large CSV file (10 GB) stored in Azure Blob Storage. You need to load the data into Power BI with optimal performance. Which THREE practices should you follow? (Choose three.)

You are loading data from a SQL Server database into Power BI. You notice that the import takes a long time because the source table contains many rows. You only need a subset of rows based on a date filter. What should you do to improve performance?

You are preparing a Power BI dataset that will be used by report authors. The source data contains a column named 'CustomerName' with leading and trailing spaces. You need to remove these spaces in Power Query. Which transformation should you use?

You are transforming data in Power Query. A column named 'SalesAmount' contains values as text with a dollar sign and thousands separator, e.g., "$1,234.56". You need to convert this column to a decimal number for analysis. What is the most efficient sequence of transformations?

You have a column 'ProductID' that contains integers. You need to ensure that this column is used as a key in relationships. What data type should the column have?

You have a Power BI dataset that refreshes daily from an on-premises SQL Server database. The refresh fails with an error 'The data source credentials cannot be used for the connection'. What is the most likely cause?

You are connecting to an Azure SQL database using DirectQuery. The database has a large table with millions of rows. Users need to see aggregated data quickly. What should you implement to improve query performance?

You need to create a date table in Power BI using DAX. Which function should you use to generate a continuous list of dates?

You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?

Which THREE of the following are best practices when preparing data in Power BI for optimal performance?

Which TWO are valid ways to combine data from multiple sources in Power Query? (Choose two.)

Your Power BI dataset uses a SQL view that joins multiple tables. You notice that some columns have null values where you expect data. You suspect the view definition has a bug. How can you verify the view's output in Power Query?

Which THREE of the following are best practices for optimizing data load performance in Power BI?

You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?

You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?

Which THREE factors should you consider when choosing between Import and DirectQuery storage modes? (Choose three.)

Which TWO of the following are valid database data source types for Power BI?

You receive a Power Query error: 'Expression.Error: The key didn't match any rows in the table.' This occurs when merging two queries. What is the most likely cause?

You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?

You are preparing an Excel workbook for import into Power BI. The workbook contains one worksheet named 'SalesData' with a well-formed table named 'tblSales' that includes headers in the first row. You want the most reliable way to import only this table so that column names and types are detected correctly. What should you do?

Which TWO options are valid methods to combine multiple tables in Power Query?

You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?

You are connecting to an on-premises Oracle database from Power BI Service. The gateway is installed and configured. However, the scheduled refresh fails with an error indicating that the data source credentials are invalid. Which TWO steps should you take to resolve the issue? (Choose two.)

You are cleaning a column that contains numbers stored as text, with occasional leading/trailing spaces and currency symbols. You apply the function above to the column. However, some rows return null even though the original text appears to be a valid number, such as '$ 1,234.56'. What is the most likely cause?

You are preparing a Power BI report that uses data from Azure SQL Database. The data includes a date column that needs to be used in time intelligence calculations. You want to ensure that the date column is recognized as a date table in the data model. What should you do?

You are importing data from a folder containing multiple CSV files with identical structure. You want to automatically combine all files into one table in Power Query. Which connector should you use?

You are building a Power BI data model with multiple fact tables and dimension tables. One of the dimension tables has a one-to-many relationship with two fact tables, but the relationships are inactive. You need to create measures that use both fact tables and the dimension table without relying on user interactions to activate relationships. What should you do?

You are designing a Power BI data model for a sales analytics solution. The source data includes a 'Sales' fact table with millions of rows and dimension tables for 'Customer', 'Product', 'Date', and 'Salesperson'. You need to minimize the model size in Power BI. Which action should you take?

You are preparing data from a CSV file that contains date values in the format 'MM/dd/yyyy'. When you load the file into Power BI Desktop, the dates appear as text. What should you do to ensure the dates are recognized as date data type?

You have a Power BI report that uses a dataset with many columns. You want to reduce the dataset size by removing columns that are not used in any report visual. What is the best practice?

Which TWO are best practices when preparing data for Power BI?

You are creating a Power BI report for a marketing team. The data is stored in a folder of CSV files on a network drive that is accessible from your computer. You need to combine all CSV files into a single table in Power BI. The files have the same structure. What should you do in Power Query?

Which TWO of the following are valid reasons to use a calculated column instead of a measure in Power BI? (Select exactly two.)

You are preparing data for a Power BI report. You have a table that contains a 'ProductID' column with some null values. You need to ensure that the 'ProductID' column does not contain any null values in the data model. Which Power Query transformation should you apply?

You are importing data from an Excel workbook that contains multiple sheets. Each sheet has similar structure but different data for different regions. You need to combine all sheets into a single table for analysis. What should you do?

You are preparing a Power BI dataset that includes a table 'Products' imported from an Excel workbook. The table has a column 'ProductCode' that contains values like 'A100', 'B200', etc. You need to create a new column that extracts the numeric part of the code (e.g., 100, 200) and uses it as an integer. Which Power Query transformation should you use?

You are connecting to a SharePoint folder that contains Excel workbooks. Each workbook has multiple sheets. You need to combine data from a specific sheet named 'Sales' across all workbooks. Which Power Query approach should you use?

You are merging two tables in Power Query: 'Orders' and 'Customers'. You want to include only rows from Orders that have a matching CustomerID in Customers. Which join kind should you use?

You are building a Power BI data model from an Azure SQL Database. The source table contains a column 'OrderDate' of type datetime. You want to create a date table in Power Query that includes all dates from the minimum to maximum OrderDate. Which M function should you use to generate the list of dates?

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?

You are importing a large dataset from a CSV file using Power Query. The file contains 50 columns, but you only need 10 for your report. What is the most efficient way to reduce the amount of data loaded into the model?

You are preparing data for a Power BI report. The source data contains a column 'FullName' with values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using Power Query. The transformation should be repeatable and not dependent on the number of spaces. What is the best approach?

Which TWO of the following are valid data source types in Power BI that support DirectQuery? (Select TWO.)

You are preparing data in Power BI Desktop. You have a table with a column 'CustomerID' that contains duplicate values. You need to create a relationship to another table that also has 'CustomerID'. However, the relationship requires unique values in at least one of the tables. What should you do?

You are a data analyst at an online retailer. You import a CSV file containing product reviews into Power BI Desktop. The file has a column named ReviewDate that currently loads as the Text data type, with values formatted like '2024-07-15T09:30:00Z'. You need to change this column to the Date/Time/Timezone data type, but when you select that type in Power Query, the transformation fails for many rows. You need to resolve the failure while preserving the original timestamp data. What should you do?

You are merging two queries in Power Query. Query 'Orders' contains columns: OrderID, CustomerID, OrderDate. Query 'Customers' contains columns: CustomerID, CustomerName, Segment. You need to add the CustomerName to the Orders query. The relationship between Orders and Customers is many-to-one. Which join kind should you use?

You need to prepare data from a folder containing multiple CSV files with identical structure. What is the most efficient way to load all files into a single table?

You have a dataset with a column 'FullName' containing values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using the space delimiter. Which Power Query transformation should you use?

You are combining data from multiple Excel files stored in SharePoint Online. Each file has the same structure but different data. You need to create a solution that automatically includes new files added to the SharePoint folder without manual intervention. What should you use?

You are a Power BI data analyst for a subscription software company. You import a table named Subscriptions from an OData feed. The table contains a column named BillingPeriod that stores values such as 'Monthly', 'Annual', and 'Quarterly'. A report author needs a numeric column that converts each value to the number of months in the billing period (1, 12, and 3 respectively) so that revenue can be normalized. You must add this column in Power Query without changing the source system. What should you do?

Which THREE are types of Power Query transforms that can be used to clean data? (Choose three.)

Which THREE of the following are best practices for data modeling in Power BI? (Select exactly three.)

A data analyst needs to combine two queries in Power Query: 'Sales2023' and 'Sales2024', both with identical column structures. Which operation should the analyst use to append the rows from 'Sales2024' to 'Sales2023'?

You are a Power BI data analyst for a logistics company. You connect to an Excel workbook stored on a SharePoint Online site. The workbook contains a table named Shipments. You need to load only the rows where the ShipmentDate is in the current year. Which Power Query transformation should you apply?

Which THREE data sources can be used with Power BI Dataflows? (Choose three.)

You are reviewing a Power Query M expression that transforms column types. The 'SalesAmount' column contains values like '1,234.56' (with a comma as thousands separator). After applying this transformation, what is the likely result?

Which TWO actions can improve data refresh performance in Power BI?

A Power BI dataset is configured to use Import storage mode. The dataset includes a fact table with 100 million rows and several dimension tables. The report is slow when users interact with visuals. You need to improve query performance without changing the storage mode. Which action should you take?

You are connecting to a SQL Server database using Import mode. The source table contains a column 'SalesAmount' with a few null values. You need to replace nulls with 0 before loading. What is the most efficient step to achieve this in Power Query Editor?

You need to combine two tables from different sources: 'Orders' from SQL Server and 'Returns' from an Excel file. Both tables have a column named 'OrderID'. You want to include all orders and only matching returns. Which join type should you use in Power Query?

You are building a Power BI report for a logistics company. The data is stored in a CSV file on a SharePoint Online document library. The CSV file is updated daily with new rows. You need to ensure that the Power BI dataset reflects the latest data every morning at 7:00 AM. The data volume is small, so full refresh is acceptable. You have already published the report to the Power BI service. What should you do to automate the refresh?

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

You are importing a CSV file into Power BI. The file contains a date column with values in the format 'MM/dd/yyyy'. However, Power Query interprets the dates as 'dd/MM/yyyy'. What should you do to correctly parse the dates?

Which TWO of the following are valid methods to combine data from multiple sources in Power BI?

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '1,234.56' and '(987.65)' for negative amounts. You need to transform this column into a decimal number. Which sequence of Power Query steps achieves this?

You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are: 1. Combine all CSV files from a folder into a single table. 2. Replace null values in ScrapUnits with 0. 3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence. 4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number). Which sequence of Power Query steps should you use?

You are preparing data for a financial model. A column named 'Amount' arrives from a source system as text values such as '1.234,56' using a European format where the period is the thousands separator and the comma is the decimal separator. You need to convert these to numeric decimal values. The dataset must refresh without manual intervention as new rows arrive. What should you do?

You are using Copilot for Power BI to assist with data preparation. Which THREE tasks can Copilot help you with?

You are a Power BI data analyst for a financial services company. You have a Power Query query that combines data from multiple Excel workbooks stored in a SharePoint folder. The workbooks have a consistent structure. You need to ensure that when new workbooks are added to the folder, the query automatically includes them upon refresh. You also need to minimize the risk of query failures due to schema changes. What should you do?

You have a Power BI dataset that includes a date table created using CALENDAR(). You need to ensure that the date table always covers the full range of dates present in the fact table, even after new data is loaded. What should you do?

A company has a Power BI dataset that imports data from a SQL Server database. The dataset includes a table with 10 million rows. The data model uses a single table and does not include any calculated columns or measures. The report users report that the dataset refresh takes too long. Which action should you take to improve refresh performance?

You are merging two tables in Power Query: Orders and Customers. The Orders table has a CustomerID column, and the Customers table has a CustomerID column. You want to keep all rows from Orders and only matching rows from Customers. Which join kind should you use?

You are working on a Power BI project for a marketing department. You have a CSV file with customer survey responses. The file contains columns: CustomerID, SurveyDate, Response (text with ratings from 1 to 5), Comments (free text). The file is 10 MB. You need to load the data into Power BI and create a measure that calculates the average rating. However, when you load the file, you notice that the Response column is imported as text instead of whole number. Also, there are some rows with missing values in the Response column. You need to ensure the data is correctly typed and handle missing values appropriately. What is the best approach?

You are building a Power BI semantic model that uses a large fact table from a data warehouse. The fact table has a date column and you want to create a date dimension. The organization requires that the date dimension includes all dates from 2010 to 2030, including weekends and holidays. What is the best practice for creating the date dimension?

You are connecting Power BI to a SQL Server database. The database contains a table with millions of sales transactions. You need to design a data model that minimizes load time and memory usage while still allowing analysis of sales by date, product, and customer. Which modeling approach should you use?

You are using the above KQL query as a source in Power Query for a Power BI semantic model. The query runs successfully but takes a long time to execute. You need to improve performance. What should you do?

You are importing data from a large CSV file (5 GB) into Power BI. The import takes too long and you need to reduce the data volume. What is the most effective approach in Power Query?

You are importing a large CSV file (200 MB) into Power BI Desktop. The import is very slow and sometimes fails. What should you do to improve performance?

You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?

Which TWO of the following are valid options when connecting to an on-premises SQL Server database from Power BI service? (Select TWO.)

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

You are a Power BI developer for a financial services company. You are preparing data from multiple sources: a CSV file containing daily stock prices (ticker, date, close_price), a SQL Server database with company information (ticker, company_name, sector), and an Excel file with quarterly earnings data (ticker, quarter, earnings_per_share). The CSV file has 5 years of daily data (approx 1.3 million rows). The SQL Server table has 5000 rows. The Excel file has 20,000 rows. You need to create a data model that allows users to filter by sector, company, and date range, and to calculate moving averages of stock prices and compare earnings over time. Performance is critical. You must decide the best approach to combine and model this data. What should you do?

You are preparing data for a Power BI report. The source data contains a column with mixed data types: some values are numbers, others are text. When loading into Power Query, the entire column is typed as text. What is the likely cause?

You are cleaning data in Power Query. A column contains customer names with inconsistent capitalization (e.g., 'john smith', 'JANE DOE'). You need to standardize the names to proper case (first letter uppercase, rest lowercase). Which transformation should you use?

You are preparing data from an Azure SQL Database. You need to ensure that sensitive columns (e.g., Social Security Numbers) are obfuscated in Power BI reports. Which TWO of the following approaches can you use? (Choose two.)

You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across multiple stores. The source data comes from an Azure SQL Database that contains a table 'Sales' with columns: StoreID, ProductID, SaleDate, Quantity, and Amount. The database also has a 'Stores' table with StoreID and StoreName, and a 'Products' table with ProductID, ProductName, and Category. You need to create a data model that supports filtering by store, product category, and date, and also allows calculation of year-over-year sales growth. You want to minimize the model size and ensure optimal performance. The data volume is large (millions of rows). You must design the data model. What should you do?

You are using Power Query to clean and transform data from a SQL Server database. You have a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'TotalAmount'. You need to ensure that the data is properly typed and that any errors are handled. Which two actions should you perform? (Choose two.)

You are importing data from an Excel workbook that has multiple worksheets. You only need data from the 'Sales' worksheet. When you connect via Power Query, all worksheets appear in the Navigator. What should you do to load only the 'Sales' worksheet?

You are a data analyst for a utility company. You import a table named MeterReadings from an OData feed. The table contains a column named ReadingTimestamp that includes date and time. You need to create two new columns: one that contains only the date and one that contains only the hour of the day as a number (0–23). You want to use built-in Power Query transformations from the Add Column tab without writing custom formulas. Which two transformations should you use? (Choose two.)

You are loading data from a folder containing multiple Excel files with identical structure. Some files have inconsistent column names due to manual edits. You need to ensure that all data is loaded correctly without errors. What should you do in Power Query?

You are building a Power BI report for a manufacturing company. You have a large fact table with 50 million rows in Azure SQL Database. You need to minimize the data refresh time and ensure that only new or changed rows are loaded. The source table has a LastModifiedDate column. What should you do?

Which TWO are valid methods to handle null values in Power Query? (Choose two.)

Which TWO actions are required when configuring a Power BI dataset to use incremental refresh?

You are a data analyst at a retail company. You have a Power BI semantic model that imports sales data from an Azure SQL Database. The database uses a timestamp column to track transaction time. You need to reduce the data refresh time and ensure that only the last 30 days of data are refreshed during each scheduled refresh. You have already created the necessary parameters rangeStart and rangeEnd in Power Query. What should you do next to implement incremental refresh?

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

You have a Power BI data model with a 'Sales' fact table and a 'Date' dimension. You need to create a calculated column in the 'Sales' table that shows the fiscal year based on a 'Date' column. The fiscal year starts on July 1. Which DAX expression should you use?

You are building a Power BI semantic model that combines data from an on-premises SQL Server database and a SharePoint Online list. The SQL Server table contains 10 million rows and updates hourly. The SharePoint list contains 500 rows and updates daily. You need to minimize the data load time and ensure the model refreshes within the scheduled 30-minute window. What should you do?

You have a table with a column 'Date' in text format (e.g., '2024-01-15'). You need to convert it to a date type. In Power Query, what is the best approach?

During data refresh in Power BI, an error occurs: 'The column 'OrderID' of the table 'Orders' contains a duplicate value and this column is part of a primary key.' The table 'Orders' is imported from an Azure SQL database. What is the most likely cause of this error?

You are a data analyst for a financial services company. You have a Power BI dataset that combines data from two sources: a CSV file in SharePoint Online and an on-premises SQL Server database. The CSV file contains exchange rates that are updated daily. The SQL Server database contains transaction data. You need to ensure that the dataset can be refreshed automatically in the Power BI service. The CSV file is updated at 6:00 AM daily, and the SQL Server database is updated continuously. You have already published the report. What should you do to enable automated refresh?

You are a data analyst for a multinational corporation. You are building a Power BI report that uses a large fact table (100 million rows) and several dimension tables. The data source is a SQL Server data warehouse. Users need to see near real-time data with a maximum latency of 15 minutes. The current import mode takes too long to refresh. You decide to use DirectQuery mode. However, queries are slow. You need to improve query performance. You consider creating aggregations in the data source. Which approach should you take in Power BI to leverage these aggregations?

Your Power BI dataset uses DirectQuery to a SQL Server data warehouse. Users report that reports are slow. You need to improve performance without changing the data source. What should you do?

A Power BI report contains a table with a column 'Date' of type date. The report users need to filter data by fiscal year, which starts on April 1. What is the best practice to support this requirement during data preparation?

You have a Power BI dataset that combines sales data from two Excel files: Sales2023.xlsx and Sales2024.xlsx. Both files have the same schema. You need to combine them into a single table without duplicating rows. What is the best approach in Power Query?

You are a Power BI developer for a healthcare organization. You are building a dataset that includes patient data from an on-premises SQL Server database. The database contains a table 'PatientVisits' with columns: PatientID, VisitDate, DiagnosisCode, and Cost. The database also has a table 'DiagnosisLookup' with DiagnosisCode and Description. You need to create a star schema in Power BI. The requirements are: - The dataset must include a date dimension table that covers all dates from 2010 to 2030. - The 'PatientVisits' table should be the fact table. - Diagnosis descriptions should be in a dimension table. - You must use Power Query to create the date dimension table using M code. - The data refresh must be scheduled daily via the on-premises data gateway. You have already loaded the 'PatientVisits' and 'DiagnosisLookup' tables. What should you do next to complete the star schema?

You are a Power BI developer at a healthcare organization. You are building a report that must comply with HIPAA regulations. You need to ensure that patient data is not exposed to unauthorized users. You plan to use Row-Level Security (RLS) with roles defined in Power BI Desktop. However, you also need to limit the data imported into the model to only necessary columns. The source is an Azure SQL Database with a table 'Patients' containing columns: PatientID, Name, SSN, Diagnosis, AdmissionDate, DischargeDate. Which two actions should you take? (Choose TWO)

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

Refer to the exhibit. You are reviewing a DAX measure in Power BI. The measure is intended to calculate total sales for the year 2024. However, when used in a visual with a slicer on 'Sales[Date]', the measure does not respect the slicer selection. What is the most likely reason?

Your organization uses Power BI to analyze sales data stored in Azure SQL Database. The data model includes a fact table with millions of rows. To improve performance, you need to reduce the amount of data loaded into the model. Which action should you take?

You are preparing data for a Power BI report. You have a query that connects to a REST API that returns JSON data. The JSON response contains a nested array of order details under an 'Orders' field. You need to expand the nested array so that each order becomes a row in the table. What should you do in Power Query?

Which TWO actions can help reduce the size of a Power BI dataset when preparing data?

You are merging two queries in Power Query: 'Orders' and 'Customers'. The 'Orders' table has a 'CustomerID' column, and 'Customers' has 'CustomerID' and 'Name'. You need to bring the 'Name' into 'Orders' but only for matching CustomerIDs; unmatched rows should be removed. Which join kind should you use?

Refer to the exhibit. You are configuring a scheduled refresh for a Power BI dataset. The exhibit shows the refresh schedule settings. The dataset is in a workspace in a Premium capacity. The scheduled refresh runs at 5:00 AM UTC daily. However, the refresh is failing consistently. What is the most likely cause?

You are designing a Power BI solution that ingests data from multiple sources: Azure Blob Storage, Salesforce, and an on-premises Oracle database. The data must be combined into a single semantic model. The Oracle database contains sensitive customer information that must be masked before being loaded. Which approach should you use to prepare the data?

You need to connect Power BI to an Excel file stored on a local network drive. The file is updated manually each morning. You want the Power BI report to always show the latest data when opened. Which data connectivity mode should you choose?

You are importing data from a CSV file that contains a column 'Date' with values like '2026-01-15'. After loading, Power Query detects the column as type 'text'. What is the recommended step to ensure the column is treated as a date?

You are importing data from a SQL Server view into Power BI. The view contains calculated columns that are expensive to compute. You want to minimize the load on the source database during refresh. What should you do?

You are using Power Query to transform a column 'FullName' containing values like 'Smith, John'. You need to split this into 'LastName' and 'FirstName' columns. Which THREE steps are required?

You need to combine two tables: Sales and Products, where Sales has a ProductID column and Products has a ProductKey column. The tables have a many-to-one relationship. Which Power Query transformation should you use?

You are designing a Power BI data model for a sales analysis. The source data has a table 'Orders' with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. You also have a table 'Customers' with CustomerID, CustomerName, and 'Products' with ProductID, ProductName. You need to create a star schema. What should you do?

You are a data analyst at a utility company. You load a table of meter readings into Power BI Desktop from an Azure SQL Database. The table has columns MeterId, ReadingTimestamp, and Consumption. You discover duplicate rows caused by a known upstream issue where the same reading is sometimes inserted twice with identical values in all three columns. You need to remove these exact duplicate rows in Power Query while keeping one copy of each reading. What should you do?

You are reviewing a Power BI dataset configuration in the service. The JSON shows a data source for an Azure SQL Database. Which statement about the configuration is correct?

You are designing a data model for a sales analysis report. The source data includes a Sales table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You also have a Customers table and a Products table. Which approach best optimizes query performance and storage?

You are preparing data for a Power BI report. The source data contains a 'CustomerName' column with values like 'John, Doe'. You need to split this column into two columns: 'FirstName' and 'LastName'. The comma is used as a delimiter, but some names have a space after the comma. Which split method should you use?

Which TWO actions should you take to reduce the size of a Power BI dataset? (Choose two.)

You are importing data from a folder containing multiple CSV files with identical structure. You use the 'Combine files' transform in Power Query. Which TWO statements are true about this process?

A company uses Power BI to analyze sales data from a SQL Server database. The database contains a table 'Sales' with 10 million rows. The business analysts need to create daily reports that aggregate sales by region and product category. To optimize report performance, which data preparation technique should be applied?

You are debugging a Power Query that imports a CSV file. The exhibit shows the M code. The CSV file contains a header row and data. Some rows have a comma inside a quoted field (e.g., "Smith, John"). What issue will arise from this code?

You are preparing data for a Power BI report at a hospital network. You connect to an Azure Synapse Analytics dedicated SQL pool. The fact table contains encounter records, and a dimension table named Patients stores protected health information. Hospital policy requires that the Patients table be filtered to only active patients before it is loaded into the semantic model, and the filter must be applied as close to the source as possible to minimize data transfer. You also need to be able to refresh the model without modifying the source database. What should you do in Power Query?

You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?

You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?

Refer to the exhibit. You are reviewing a Power BI data source credential configuration. The Azure Blob Storage data source uses 'Anonymous' credentials. However, the refresh fails with an error indicating that the blob container is private and requires authentication. Which change should you make?

You are a data analyst for a global retail company. The company uses Power BI Premium capacity. You are building a dataset that combines sales data from three sources: 1. An Azure SQL Database that stores transactional sales data (10 million rows per day, retained for 5 years). 2. A SharePoint Online folder containing monthly Excel reports from regional offices (each report has a different structure). 3. A Dataverse table that contains customer feedback scores. Requirements: - The dataset must support near real-time reporting for the current month's sales (maximum 15-minute latency). - Historical sales data (older than current month) can be refreshed daily. - Customer feedback scores should be updated every hour. - The Excel reports from SharePoint must be combined into a single table with consistent columns. - The final dataset should be optimized for fast query performance. You need to design the data preparation strategy. What should you do?