Power QueryExercises

Pivot and unpivot quiz

Reshaping data and surviving new columns on refresh.

Revise first: Unpivot columns so new months are picked up, Pivot a column with an aggregation, Pivot without aggregation and the duplicate error, Unpivot, then turn header text into real dates

Question 1

1.How many rows?

Table.RowCount(Table.UnpivotOtherColumns(#table({"Item", "Jan", "Feb", "Mar"}, {{"A", 1, 2, 3}, {"B", 4, 5, 6}}), {"Item"}, "M", "V"))
Question 2

2.How many rows?

Table.RowCount(Table.UnpivotOtherColumns(#table({"Item", "Jan", "Feb"}, {{"A", 1, null}}), {"Item"}, "M", "V"))
Question 3

3.Which unpivot picks up a new month column automatically on refresh?

Question 4

4.What does this return?

Table.Pivot(#table({"R", "S", "Q"}, {{"N", "A", 1}, {"N", "A", 2}, {"N", "B", 5}}), {"A", "B"}, "S", "Q", List.Sum){0}[A]
Question 5

5.The table has Region, Customer, Status and Amount. You pivot Status (values from Amount) with Customer still in the table. What happens?

Question 6

6.What does 'Don't aggregate' do when a cell would receive two values?

Question 7

7.Unpivoting produces Attribute and Value columns. What should you do?

Question 8

8.Which layout do analysis tools usually prefer?

0 of 8 answered. Unanswered questions count as wrong.