At another time, you may need to pivot columns so that you can aggregate values for each unique value in a column. For example, if you need to know how many opportunities are there in each stage and what the total amount is for each stage, you may use a pivot table. Just like Excel, the Power BI Designer works the same way.
To build a good example for this, use the Pivot by Sales Stage dataset we created earlier. The new dataset needs to eventually show the count of opportunities and total amounts by Sales Stage.
This is how you do it:
Open the Pivot by Sales Stage dataset in the query window and select the column that you want to pivot from. In our case, this will be the Sales Stage column.
After you have the column selected, navigate to the Transform | Pivot Column tab from the main ribbon to open the pivot window:
Once the Pivot Column window appears, it will show you the column that you will use to pivot the data on. Select Estimated Amount from the Values Column pick list....