Task 1.2: Profile and clean the data

• ATurn on Column profile for Amount for deeper profiling

  Incorrect

  Column profile adds detail, but it uses the same top-1,000-row sample by default.

• BTurn on Column distribution for Amount

  Incorrect

  Distribution charts also use the default sample, so errors further down stay hidden.

• CSelect Amount and run Detect data type

  Incorrect

  Type detection inspects the first rows to pick a type; it doesn't extend profiling to all rows.

• DChange column profiling to the entire data set

  Correct

  Profiling runs on the first 1,000 rows by default; this setting makes it cover all rows.

The data profiling tools (column quality, column distribution and column profile) work over the first 1,000 rows by default. Selecting the Column profiling based on top 1000 rows message in the lower-left corner switches them to Column profiling based on entire data set, so errors in later rows show up in the percentages.

• AIn Options, change the current file's regional settings to English (United Kingdom) for the London file

  Incorrect

  A file-level locale change affects every query in the file, so the US-formatted dates would then fail or flip.

• BOn the London query, use Change type > Using locale with Date and English (United Kingdom)

  Correct

  The conversion for that column interprets the text as UK dates, while every other query keeps the file's US locale.

• CChange OrderDate's type to Date, then use Replace errors with null

  Incorrect

  This turns every date with a day above 12 into a blank instead of converting it.

• DChange OrderDate's type to Text to avoid conversion errors

  Incorrect

  Text dates can't drive date filters or time intelligence, so the column still isn't usable as a date.

Power Query uses a locale to interpret text when it converts it to another type; in Power BI Desktop it defaults to the operating system's regional format, and you can override it for the whole file under Options > Current file > Regional settings. For a single column that uses another format, Change type > Using locale converts it with the locale you choose, such as English (United Kingdom) for day/month/year.

• AChange the type to Whole Number, and then replace error values with 0

  Incorrect

  The NA cells become 0, which counts them as zero units.

• BChange the type to Whole Number, and then select Remove errors

  Incorrect

  Remove errors deletes the rows whose conversion failed, so rows are dropped.

• CChange the type, then Keep errors

  Incorrect

  Every row that converted cleanly is dropped, so most rows are lost.

• DReplace NA with null, then change the type to Whole Number

  Correct

  Nulls convert without errors and stay blank, so they aren't counted as zero and every row is kept.

Converting a text column that contains values such as NA to a number causes cell-level conversion errors. You can fix the values before the conversion, for example by replacing NA with null, or handle the errors afterwards. Here, handling them afterwards either turns NA into a number or drops rows.

• ATrim the Email column

  Correct

  Trim removes leading and trailing whitespace, so the trailing space no longer makes the values differ.

• BClean the Email column

  Incorrect

  Clean doesn't change letter case or remove ordinary spaces, so both variants remain.

• CFormat the Email column as lowercase

  Correct

  Power Query compares text case-sensitively, so converting to one case lets the duplicates match.

• DKeep duplicates on the Email column

  Incorrect

  Keep duplicates filters to the repeated values; it doesn't make different spellings match.

• EFill down the Email column

  Incorrect

  Fill down replaces nulls with the value above; it doesn't normalize existing values.

Remove duplicates compares values exactly, and Power Query is case-sensitive, so values that differ only by case or by extra spaces stay separate. The documentation suggests applying an uppercase or lowercase transform before removing duplicates; trimming removes leading and trailing whitespace.

• AApply Remove duplicates to the Customer column so only one spelling is left

  Incorrect

  That would collapse the rows to one per name, removing customer rows instead of fixing how the name is shown.

• BSet the Customer column's data category to Person in the model

  Incorrect

  A data category describes the column for visuals and maps; it doesn't change the stored value.

• CApply Capitalize Each Word to the Customer column in Power Query

  Correct

  Once every row has the same capitalization, the model has only one variant to store and show.

• DChange the column's data type to Text in Power Query Editor

  Incorrect

  The column is already text; the type doesn't control which capitalization the engine keeps.

Power Query is case sensitive and shows values as stored in the source, but the engine that stores and queries data in Power BI is case insensitive. While loading, it keeps one dictionary entry for values that differ only by case, and every row refers to the first variant it met. Normalizing capitalization in Power Query, for example with Capitalize Each Word, gives a consistent value.

• AColumn quality

  Correct

  Column quality labels values as valid, error or empty and shows each share as a percentage.

• BColumn distribution

  Incorrect

  Column distribution shows value frequencies with distinct and unique counts.

• CQuery dependencies

  Incorrect

  Query dependencies shows how queries reference each other and their sources.

• DFormula bar

  Incorrect

  The formula bar shows a step's M formula, not data quality.

The data profiling tools are column quality, column distribution and column profile, turned on from the View tab. Column quality shows the share of valid, error and empty values (plus unknown and unexpected error) under each column name.

• AOpen the Advanced Editor for the query

  Incorrect

  The Advanced Editor shows the query's M code, not the details of one cell's error.

• BSelect the white space next to Error in one of those cells

  Correct

  Selecting the white space in an error cell opens the error pane under the data preview, with the error's reason and message.

• CStart Query diagnostics for the query

  Incorrect

  Query diagnostics records what Power Query does while it evaluates queries; it is not where one cell explains its error.

• DOpen the data type list on the Price column header

  Incorrect

  The type list changes the column's type; it doesn't explain an error that's already there.

A cell-level error doesn't stop the query from loading; the cell shows Error instead of a value. Selecting the white space in the cell opens the error pane underneath the data preview, which shows the error reason, such as DataFormat.Error, and the error message. You can examine one error value at a time this way.

• AApply Fill down instead, which treats empty text as null

  Incorrect

  Fill down also fills only null cells, so the empty text stays as it is.

• BChange the column type to Whole Number

  Incorrect

  Comments are text; a number type would only create errors.

• CRemove blank rows from the table

  Incorrect

  This would delete rows rather than fill their comments.

• DReplace the empty values with null, then apply Fill up

  Correct

  Fill up fills null cells, so the empty text must become null first.

Fill up and fill down replace null values with the nearest nonempty value. Cells with empty text aren't null, so first use Replace values with an empty Value to find and null as Replace with, then fill.

• AIn File > Options, under Report settings

  Incorrect

  Report settings cover report behavior, not the Power Query preview.

• BOn the Home tab, under Manage Parameters

  Incorrect

  Manage Parameters creates and edits query parameters.

• COn the View tab, in the Data preview group

  Correct

  The profiling options are check boxes in the View tab's Data preview group.

• DIn the Transform tab, under Detect data type

  Incorrect

  Detect data type sets column types; it doesn't show profiling.

To enable the data profiling tools in Power Query Desktop, go to the View tab and select the options you want in the Data preview group: column quality, column distribution and column profile.