Task 1.3: Transform and load the data

• ASelect all the month columns and choose Unpivot only selected columns

  Incorrect

  The step lists today's month columns by name, so a column added next month stays as a column.

• BSelect the Country column and choose Transpose

  Incorrect

  This doesn't produce Month and Amount columns; the countries would become columns instead.

• CSelect the Country column and choose Unpivot other columns

  Correct

  Every column except Country is unpivoted at refresh, so new month columns are included automatically.

• DSelect the month columns and choose Pivot column

  Incorrect

  Pivoting the month columns doesn't produce Month and Amount rows.

Unpivoting turns columns into attribute-value pairs, an Attribute column with the old headers and a Value column with their values. Unpivot other columns keeps the selected columns and unpivots all the others, so columns added to the source later are unpivoted too. Unpivot only selected columns fixes the list of columns to the ones selected.

• AGroup by Region and CustomerID, then group that result by Region with Count rows

  Correct

  The first grouping leaves one row per customer in each region, so counting rows per region counts distinct customers.

• BGroup by Region with the Count distinct values operation on CustomerID

  Incorrect

  Count distinct values is available only in Power Query Online, not in Power BI Desktop.

• CGroup by Region with the Count rows operation

  Incorrect

  This counts orders per region, so repeat customers are counted more than once.

• DGroup by Region with the Count distinct rows operation

  Incorrect

  This counts distinct whole order rows, which differ by order even for the same customer.

Group by summarizes rows by one or more columns with operations such as Sum, Average, Count rows, Count distinct rows and All rows. Count distinct values and Percentile are available only in Power Query Online. In Power BI Desktop, grouping by Region and CustomerID first leaves one row per customer per region, and a second grouping by Region with Count rows gives the distinct customer count.

• ASplit Payload by the comma delimiter into columns

  Incorrect

  Splitting treats JSON as plain text, leaving braces, quotes and property names in the values.

• BSelect Payload and choose Parse > JSON

  Correct

  Parsing turns each text value into a record, which you can then expand into columns.

• CConnect to the Payload values with the JSON connector

  Incorrect

  The JSON connector reads JSON files or web responses, not a column inside an existing query.

• DTranspose the table so Payload becomes a row

  Incorrect

  Transposing doesn't interpret the JSON text, so the properties stay inside one value.

Power Query can parse a column of text strings as JSON or XML with the Parse option on the Transform or Add column tab. A JSON object becomes a record value that you expand to columns, and a JSON array becomes a list. The JSON connector is for JSON files or web sources.

• AClear Enable load on Holidays

  Incorrect

  The Holidays table would leave the model, but it must stay.

• BClear Enable load on Orders_Raw and Customers_Raw

  Correct

  The staging queries still feed the merge, but their tables aren't loaded into the model.

• CDelete Orders_Raw and Customers_Raw after the merge step runs

  Incorrect

  The merge refers to both queries, so deleting them breaks Sales.

• DClear Include in report refresh on Holidays

  Correct

  The table stays loaded in the model and is skipped by report refresh, including refresh in the service.

• EHide Orders_Raw and Customers_Raw in report view to skip their refresh

  Incorrect

  Hidden tables are still loaded and refreshed, so they still use memory and refresh time.

Intermediate queries that only feed other queries don't need to be loaded: clearing Enable load keeps them available to the queries that reference them without loading them into the model. For data that rarely or never changes, clearing Include in report refresh keeps the loaded table but excludes it from report refresh, and such queries are also excluded from refresh in the service.

• AWhole number

  Incorrect

  Whole numbers drop the cents entirely.

• BFixed decimal number

  Correct

  It stores four digits after the decimal separator, avoiding the small rounding drift of floating-point numbers.

• CDecimal number with more decimals displayed

  Incorrect

  Changing the display format doesn't change the floating-point storage that causes the drift.

• DText

  Incorrect

  Text can't be summed in measures.

Power BI has three number types: Decimal number (floating point), Fixed decimal number and Whole number. Fixed decimal number has a fixed four digits to the right of the decimal separator and suits currency, where small floating-point errors can accumulate; values past four digits are truncated.

• ARename Qty to Quantity in March before appending

  Correct

  Append matches columns by name, so the same name puts every month's values in one column.

• BMerge the three queries on Quantity instead of appending them

  Incorrect

  A merge joins tables side by side on matching values; it doesn't stack the months.

• CApply Fill down to the Quantity column after the append

  Incorrect

  Fill down copies the previous row's value into nulls, which puts wrong quantities on March rows.

• DAppend the March query first so its column order wins

  Incorrect

  The order of tables doesn't matter; columns are matched by header, not position.

An append stacks the rows of two or more tables and builds the schema from all their column headers. When the headers differ, every header appears in the result, with null in the columns a table doesn't have. Giving the columns the same name before appending puts the values in one column.

• ADecimal number

  Correct

  A Duration column converts into a Decimal number at load, so it can be added to dates and shown as a magnitude.

• BDuration

  Incorrect

  Duration is available in Power Query Editor but not in the model, which has no Duration type.

• CText

  Incorrect

  The values don't load as text; they become numbers.

• DDate/Time

  Incorrect

  Date/Time/Timezone columns become Date/Time; Duration columns become numbers.

Power Query Editor has two data types that Table view and Report view don't have: Date/Time/Timezone and Duration. When such a column loads into the model, Date/Time/Timezone converts into Date/Time and Duration converts into Decimal number, which you can add to or subtract from Date/Time values and use in visuals that show magnitude.

• ASplit column by delimiter, using a space

  Incorrect

  The codes contain no space or other delimiter to split at.

• BUnpivot the ProductCode column

  Incorrect

  Unpivoting turns columns into attribute-value rows; it doesn't split a value.

• CSplit column by number of characters: 2, once as far left as possible

  Correct

  The first two characters go to one column and the rest of each code to a second column.

• DGroup by the ProductCode column

  Incorrect

  Group by summarizes rows by their values; it doesn't separate text inside a value.

Split column by number of characters divides text at a fixed position. With Once, as far left as possible, it splits once after the given number of characters and returns two columns, named with .1 and .2 suffixes, which you can rename and type. Splitting by delimiter needs a character that separates the parts.

• AHide the other 35 columns, keeping five visible in report view

  Incorrect

  Hidden columns are still loaded into the model.

• BClear Enable load on the query

  Incorrect

  Enable load is a setting for the whole query; it can't keep some columns and drop others.

• CSelect the five columns and choose Remove other columns

  Correct

  It keeps the selected columns and removes every other one in a single step.

• DTranspose the table and remove rows

  Incorrect

  Transposing turns columns into rows, which isn't needed here.

Choose columns and Remove other columns keep only the columns you select, in one step. Hiding a column only removes it from the field list.

• ASet an explicit data type for each of those columns

  Correct

  Guidance is to define column types explicitly for unstructured sources and to avoid Any columns in the query output.

• BKeep Any so that every kind of value fits the columns

  Incorrect

  Any only means that no type was defined, and guidance says to avoid it in the output.

• CChange both columns to the Binary type

  Incorrect

  Binary represents binary-format data such as files, not ordinary values.

• DHide both columns in Model view after they load

  Incorrect

  Hiding changes what the field list shows; the columns still have no defined type.

Any is the status given to a column that doesn't have an explicit data type definition. For unstructured sources such as CSV, Excel and text files, guidance recommends defining column data types explicitly and not leaving Any columns in the query output. You can set a type from the icon on the column heading, from the Transform tab, or with Detect data type.