PL-300 Prepare the data Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Use the 'Combine Files' feature with a sample file, then in the transformation step, promote headers and rename columns using a mapping table.

  Why this is correct

  When you connect to a folder, Power Query's Combine Files feature treats the first file as a sample, generates a binary parse function, and applies it across all files. After combining, you typically promote the file's initial data row to headers and then use a mapping table to rename arbitrary column titles (e.g., 'Price', 'PRICE', 'pricing') to a consistent schema. This standardizes variations across workbooks and avoids duplicate or misaligned columns when loading into the data model. It also allows you to dynamically refresh as new files are added, reusing the same transformation logic.

• ✗

  Use 'Merge Queries' to join the files based on row position.

  Why it's wrong here

  Merge Queries is intended to join two distinct queries by matching key columns (like a SQL join), not to reconcile column-name inconsistencies across a folder of files. Using 'row position' as the join key makes the merge dependent on the physical ordering of rows inside each Excel file; any sheet with a different sort order, an extra header row, or inserted comments will break the correspondence. Even if the merge succeeded, it would not promote headers or rename columns, so the resulting table would still contain mismatched field names from each file. This approach is neither schema-aware nor robust for variable input layouts.

• ✗

  Change the data source to a SharePoint folder and use 'Load to Data Model' directly.

  Why it's wrong here

  Switching the source from a local folder to a SharePoint folder only changes where the files are stored and accessed; it does nothing to reconcile inconsistent column headers. Loading directly to the Data Model in Power Query Desktop without an explicit transformation step will import the raw Excel content as-is, so files with different column names or order will produce a messy, ambiguous query or errors. The missing schema standardization remains the central problem, and the data model requires uniform column names to create relationships efficiently. Moreover, SharePoint source still requires a Combine Files step to consolidate multiple workbooks into a single feed, just like a local folder.

• ✗

  In Power Query, use 'Enter Data' to manually create the schema.

  Why it's wrong here

  The 'Enter Data' dialog in Power Query lets you manually type or paste a small static table, which is a quick way to define a constant lookup or mapping table—not a scalable way to load an unknown number of business files. Each new Excel file would require you to manually copy its contents and re-paste them, and the moment any file changes you must redo the manual work. It completely bypasses the folder or SharePoint connector, so there is no automatic refresh pipeline. This is acceptable only for tiny, fixed datasets, not for a dynamic folder-based import scenario.

Go deeper

Related to this question