PL-300 · Question #387
You have the CSV file shown in the following table. You use Power Query Editor to preview the data in the file. You need to transform the data to meet the following requirements: - The first column…
The correct answer is B. unpivot. To transform data with month columns into a structure with Month, Year, and Order Amount, you should first unpivot the month columns.
Question
You have the CSV file shown in the following table. You use Power Query Editor to preview the data in the file. You need to transform the data to meet the following requirements:
- The first column must contain the month.
- The second column must contain the year.
- The third column must contain the order amount for the month and
year. Which transformation should you use first?
Options
- Aremove
- Bunpivot
- Ctranspose
- Dpivot
How the community answered
(31 responses)- A6% (2)
- B77% (24)
- C3% (1)
- D13% (4)
Why each option
To transform data with month columns into a structure with Month, Year, and Order Amount, you should first unpivot the month columns.
Remove deletes columns or rows, which would lose data necessary for the transformation.
Unpivot transforms columns (like individual months) into attribute-value pairs, creating a new column for the attribute (Month) and another for the corresponding values (Order Amount), which is essential for reshaping "wide" data into a "tall" format suitable for analytical models. This operation effectively gathers all the monthly sales data under a single column and identifies the respective month for each sale.
Transposing swaps rows and columns, which would likely not result in the desired Month, Year, OrderAmount structure from the described input.
Pivot transforms unique values from one column into new columns, which is the inverse of the required operation.
Concept tested: Power Query unpivot transformation
Source: https://learn.microsoft.com/en-us/power-query/unpivot-columns
Community Discussion
No community discussion yet for this question.