Question
You receive annual sales data that must be included in Power BI reports.
From Power Query Editor, you connect to the Microsoft Excel source shown in the following exhibit.
| Month | MonthNumber | 2019 | 2020 | 2021 |
|---|---|---|---|---|
| Jan | 1 | 345 | 5526 | 3456 |
| Feb | 2 | 758 | 773 | 0 |
| Mar | 3 | 37763 | 570 | null |
| Apr | 4 | 8364 | 9417 | null |
| May | 5 | 58256 | 276 | null |
| June | 6 | 6722 | 235 | null |
| July | 7 | 55225 | 6297 | null |
| Aug | 8 | 673 | 63 | null |
| Sep | 9 | 552 | 357 | null |
| Oct | 10 | 7838 | 24214 | null |
| Nov | 11 | 83544 | 257 | null |
| Dec | 12 | 32455 | 389 | null |
You need to create a report that meets the following requirements:
- Visualizes the Sales value over a period of years and months
- Adds a slicer for the month
- Adds a slicer for the year
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Explanation
Each year is stored as its own column, so the data must be unpivoted to get one Year column and one Sales column. Selecting Month and MonthNumber and choosing Unpivot other columns turns every other column into Attribute/Value pairs, and it also picks up any year columns added later. Renaming Attribute to Year and Value to Sales then gives fields for the visual and both slicers. Transpose would swap rows and columns and not produce a usable year field.
