PL-300: Prepare the Data Domain Explained

Of the four PL-300 domains, Prepare the Data (25-30% of the exam) is the one everything else depends on. A beautiful dashboard built on dirty, badly-shaped data is worthless. This domain tests whether you can get data into Power BI from any source, judge its quality, and reshape it cleanly using Power Query.

This guide walks through each skill in the domain with concrete steps you can follow in Power BI Desktop, using a fictional Indian retail dataset.

Getting data from sources

Power BI connects to hundreds of sources through the Get Data button. For the exam, know these categories:

• Files - Excel, CSV/Text, JSON, PDF, and folders of files.
• Databases - SQL Server, MySQL, PostgreSQL, Oracle.
• Online services - SharePoint, Dataverse, web pages.
• Power Platform and others - dataflows, OData feeds.

When you connect, Power BI launches the Navigator so you can preview and select tables before loading. A common exam scenario is combining many files: pointing the Folder connector at a directory of monthly sales CSVs and letting Power BI append them all into one query automatically.

Import vs DirectQuery vs Dual

This is a guaranteed exam topic. Choosing a storage mode is a real trade-off.

• Import loads a copy of the data into Power BI's in-memory engine. It is the fastest for report interaction and unlocks all DAX and Power Query features, but data is only as fresh as the last refresh. Best for most scenarios.
• DirectQuery leaves data in the source and sends queries live as users interact. Use it when data is too large to import, must be near real-time, or compliance forbids copying it. The trade-off is slower visuals and some feature limitations.
• Dual lets a table behave as Import or DirectQuery depending on the query, useful in composite models.

Exam tip: if a question stresses "very large dataset," "real-time," or "data cannot leave the source," the answer is usually DirectQuery. If it stresses performance and rich transformations, choose Import.

Profiling data quality in Power Query

Before transforming, you must understand your data. The Power Query Editor has three profiling tools on the View tab. Turn them all on.

• Column quality shows the percentage of valid, error, and empty values in each column. A pincode column showing 4% errors tells you exactly where to look.
• Column distribution shows distinct and unique counts, instantly revealing, say, that a state column has 31 distinct values when India has 28 states plus 8 union territories - a sign of duplicates like "Tamilnadu" and "Tamil Nadu."
• Column profile gives detailed statistics for a selected column: min, max, average, error and empty counts, and a value-distribution chart.

By default profiling runs on the top 1,000 rows. For a thorough check, switch it to column profiling based on entire data set at the bottom of the window.

Essential Power Query transforms

The bulk of this domain is shaping data. Here are the transforms you must know cold, all available by right-clicking a column or using the Transform and Home ribbons.

Removing rows and columns

• Remove Top Rows to delete title or logo rows from an Excel export.
• Remove Blank Rows and Remove Duplicates to clean up.
• Choose Columns / Remove Columns to keep only what you need.
• Keep / Remove Errors to handle rows that failed type conversion.

Fixing data types

Power Query guesses types, but verify every column. Set order_date to Date, amount to Decimal Number, and pincode to Text so leading zeros survive. Wrong types are a frequent root cause of broken visuals and DAX.

Splitting and merging columns

• Split Column by delimiter to break "Mumbai, Maharashtra" into city and state.
• Merge Columns to combine first and last name into a full name.
• Use Extract to pull, say, the first four digits of a GSTIN.

Replacing and cleaning values

• Replace Values to standardise "Bangalore" to "Bengaluru".
• Format transforms like Trim, Clean, and Capitalize Each Word fix whitespace and casing - invaluable for inconsistent Indian city names.

Pivoting and unpivoting

This trips up many candidates. Unpivot turns wide data into tall data, which is what a good model needs. If you have monthly sales spread across twelve columns (Jan, Feb, ... Dec), select them and Unpivot Columns to get two tidy columns: Month and Sales. Pivot does the reverse, turning rows into columns.

Merge and append queries

• Append Queries stacks tables with the same columns - like combining North, South, East and West region files into one. This is a UNION.
• Merge Queries joins tables on a key column, like a SQL JOIN - for example attaching a region from a lookup table to each order via city. Know the join kinds: left outer, right outer, inner, and the anti joins.

Applied steps and Query Settings

Every transform you apply is recorded as an Applied Step in the Query Settings pane on the right. This is Power Query's superpower: the steps are repeatable, so when the source refreshes next month, every cleaning step re-runs automatically. You can rename, reorder, or delete steps, and the underlying language is M, which you can view in the formula bar.

Exam pointers

• Be able to justify Import vs DirectQuery from a scenario.
• Know which profiling tool reveals which problem (quality = nulls/errors, distribution = distinct/unique counts).
• Distinguish Append (stack rows, UNION) from Merge (join on key, JOIN).
• Understand Unpivot and when you would use it.
• Remember that Applied Steps make the whole process repeatable on refresh.

Master this domain and you build everything else on solid ground. Sloppy data preparation is the single most common reason dashboards fail in the real world - and the most common reason candidates lose marks here.

Related: Top 25 Power BI Interview Questions with Real Answers · Take a PL-300 mock exam

Don't just read. Prove your skill on DevWithData.