PL-300 Prepare the data Practice Test 15 — 15 Questions

Question 1

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?

Accepted Answer

In Power Query Editor, use Append Queries to combine the sheets.. Option B is correct because the Append Queries operation in Power Query Editor is designed to combine rows from multiple tables or queries with similar column structures into a single table. Since each sheet in the Excel workbook contains data for different regions with the same structure, appending them stacks the rows, creating a unified dataset for analysis.

Answer

In Power Query Editor, use Merge Queries to combine the sheets.

Answer

Copy and paste the data from each sheet into a master sheet in Excel.

Answer

In Power Query Editor, use Group By to consolidate the data.

Question 2

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?

Accepted Answer

In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.. Option C is correct because Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.

Answer

In Power Query Editor, split the column by comma and then use the second part.

Answer

In DAX, create a calculated column using VALUE() after removing commas.

Answer

In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.

Question 3

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?

Accepted Answer

Use the USERELATIONSHIP function in the measure definition.. Option C is correct because the USERELATIONSHIP function in DAX allows you to temporarily activate an inactive relationship within a measure definition. This enables you to use a dimension table with multiple fact tables without changing the model's default active relationships or relying on user interactions. By specifying the inactive relationship in the measure, you can perform accurate aggregations across both fact tables while maintaining the intended data model structure.

Answer

Merge the fact tables into one table.

Answer

Create calculated columns in the dimension table to relate to each fact table.

Answer

Change both relationships to active.

Question 4

You are importing data from a JSON file that contains nested arrays. You need to expand the arrays into separate rows while maintaining the parent information. What should you do?

Accepted Answer

In Power Query Editor, click the expand icon on the column and select 'Expand to New Rows'.. Option A is correct because Power Query's 'Expand to New Rows' function is specifically designed to flatten nested arrays (e.g., lists or records within a JSON column) into separate rows while duplicating the parent row's other columns. This operation uses the `Table.ExpandListColumn` or `Table.ExpandRecordColumn` M function under the hood, which unpacks each element of the array into its own row, preserving the parent context.

Answer

Use the Pivot Column feature to transform the array.

Answer

Use the Merge Queries feature to combine tables.

Answer

Use the Group By feature to flatten the data.

Question 5

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?

Accepted Answer

Use a SQL query with a WHERE clause in the Power Query Editor.. Option B is correct because using a SQL query with a WHERE clause in Power Query Editor pushes the date filter down to the SQL Server database, reducing the amount of data transferred over the network and imported into Power BI. This query folding technique leverages the database engine's indexing and processing power, which is far more efficient than filtering after import. By retrieving only the necessary rows upfront, you minimize both network latency and memory consumption in Power BI.

Answer

Remove unnecessary columns in Power Query.

Answer

Load all data and then apply a filter in Power BI.

Answer

Enable incremental refresh on the dataset.

Question 6

You have a Power BI data model that contains a fact table and a date dimension. The fact table has a column 'OrderDate' and the date dimension has a column 'Date'. You need to create a measure that calculates the total sales for the last complete month. The date dimension is marked as a date table. What DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date])). Option D is correct because PREVIOUSMONTH returns a set of dates for the previous month relative to the last date in the current filter context. Since the date dimension is marked as a date table, time intelligence functions like PREVIOUSMONTH work correctly. CALCULATE then evaluates SUM(Sales[Amount]) over that month-long period, giving total sales for the last complete month.

Answer

CALCULATE(SUM(Sales[Amount]), DATESMTD('Date'[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), 'Date'[Date] = DATE(2023,1,1))

Answer

TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Question 7

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

Accepted Answer

Append Queries. Append Queries is a valid method to combine data from multiple sources in Power BI because it stacks rows from two or more tables into a single table, which is essential when you have similar data structures across different sources (e.g., monthly sales files). This operation is performed in Power Query Editor and corresponds to a UNION operation in SQL, making it a standard data preparation technique.

Answer

Pivot Column

Answer

Unpivot Columns

Answer

Group By

Question 8

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

Accepted Answer

Set correct data types for all columns.. Setting correct data types for all columns (Option C) is a best practice because it ensures that Power BI uses the most efficient storage and processing methods. Incorrect data types can lead to increased memory usage, slower query performance, and inaccurate calculations, as Power BI must perform implicit conversions at query time.

Answer

Merge all tables into a single table for simplicity.

Answer

Create calculated columns instead of measures when possible.

Question 9

Which TWO of the following are valid ways to create a date table in Power BI?

Accepted Answer

Using DAX function CALENDAR or CALENDARAUTO.. Option D is correct because the DAX functions CALENDAR and CALENDARAUTO are specifically designed to generate a single-column table of dates, which can then be used as a date table. CALENDAR requires explicit start and end dates, while CALENDARAUTO automatically scans the model to determine the date range. Both are standard methods for creating a date table in Power BI.

Answer

Using the time intelligence functions like TOTALYTD.

Answer

Marking an existing table as a date table.

Answer

Enabling the auto date/time feature.

Question 10

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?

Accepted Answer

Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.. Option B is correct because it follows the star schema best practice: importing dimension tables (Stores, Products, a dedicated Date table) and the fact table (Sales) separately, then creating relationships. This minimizes model size by avoiding data duplication and enables efficient filtering by store, product category, and date. The separate date table is essential for accurate year-over-year calculations using DAX time intelligence functions like SAMEPERIODLASTYEAR, which require a continuous date range.

Answer

Import all tables as they are and create a single flat table by merging Sales, Stores, and Products in Power Query.

Answer

Import Sales table only and create calculated columns for StoreName and ProductName using RELATED.

Answer

Import Sales table and use the auto date/time feature for time intelligence.

Question 11

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?

Accepted Answer

Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.. Option C is correct because importing all tables into the in-memory VertiPaq engine ensures optimal performance for large datasets (1.3M rows) and complex calculations like moving averages. Creating a separate date table with CALENDAR enables proper time intelligence, while the bridge table resolves the many-to-many relationship between quarterly earnings and daily dates, allowing accurate filtering by sector, company, and date range without performance degradation.

Answer

Use DirectQuery for StockPrices (CSV) and import the other tables.

Answer

Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.

Answer

Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.

Question 12

You are a business analyst at a marketing agency. You receive a weekly export from a web analytics platform as a CSV file. The file contains columns: Date, Campaign, Impressions, Clicks, Conversions. You need to create a Power BI report that shows trends over time, with the ability to filter by campaign. The CSV file is updated weekly and you want to automate the refresh. You also want to ensure that any new campaigns are automatically included in the report. You have limited experience with Power BI. What should you do?

Accepted Answer

Create a Power BI dataflow that reads the CSV file from a SharePoint folder, transforms the data (e.g., change data types), and then load the dataflow as a dataset into Power BI. Schedule the dataflow to refresh weekly.. Option D is correct because it uses a Power BI dataflow to read the CSV from a SharePoint folder, which supports scheduled refresh without a personal gateway. The dataflow can automatically include new campaigns as they appear in the folder, and the scheduled weekly refresh ensures the report stays up to date. This approach is ideal for users with limited Power BI experience because dataflows handle transformation and refresh centrally in the Power BI Service.

Answer

Use Power Query to read the CSV from a local folder, but schedule the refresh using a personal gateway.

Answer

Import the CSV file directly into Power BI Desktop, create the report, and publish to Power BI Service. Manually re-import the CSV each week.

Answer

Enable incremental refresh on the dataset to only load new data each week.

Question 13

You are creating a Power BI dataset from a SQL Server data warehouse. The warehouse contains a fact table with 500 million rows and dimension tables. You need to minimize the data refresh time while ensuring that the dataset meets the reporting requirements. Which approach should you recommend?

Accepted Answer

Use Import mode but filter rows and reduce columns in Power Query to only those needed.. Option D is correct because importing only the necessary columns and rows reduces the data volume, which directly minimizes refresh time. With 500 million rows, Import mode is generally faster than DirectQuery for large fact tables in Power BI, as it avoids querying the source on every interaction. Filtering and column reduction in Power Query ensures the dataset remains lean while meeting reporting requirements.

Answer

Create a composite model using DirectQuery for the fact table and Import for dimensions.

Answer

Use DirectQuery mode for the dataset.

Answer

Configure incremental refresh on the fact table.

Question 14

You are combining CSV files from a folder. The code above returns an error because some CSV files have inconsistent column names and data types. You need to modify the solution to handle variations in column structure. What should you do?

Accepted Answer

Use the Folder connector's 'Combine Files' transform, which handles schema merging automatically.. Option D is correct because the 'Combine Files' transform in Power Query (Folder connector) automatically detects and merges columns from multiple CSV files, even when column names or data types vary. It uses a sample file to infer the schema and then applies transformations to handle inconsistencies, such as promoting headers and aligning data types, which resolves the error without manual intervention.

Answer

Change the delimiter to a semicolon and reconfigure the CSV parser.

Answer

Use Table.RemoveRows to remove rows that cause errors.

Answer

Add a custom column to each file to standardize data types before combining.

Question 15

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?

Accepted Answer

Use Replace Values to remove '$' and ',', then change data type to Decimal Number.. Option C is correct because it explicitly removes both the dollar sign and the comma before changing the data type, ensuring that Power Query can interpret the cleaned text as a decimal number without errors. Directly changing the data type (Option B) would fail because Power Query cannot automatically parse currency symbols and thousands separators from text. Option A is inefficient because splitting the column is unnecessary when simple replacements suffice. Option D is identical to C but omits the comma removal, which would leave the thousands separator and cause conversion failures.

Answer

Split the column by delimiter and keep the numeric part, then change data type.

Answer

Change data type to Decimal Number directly; Power Query will automatically clean the values.

Answer

Use Replace Values to remove '$' and ',' then change data type to Decimal.