How to Pivot and Group a table?

[saipawar]

Hi, 

I have events data with metrics and year that I'd like to use to build views for comparitive study.

Here,'s the input and desired output in Power BI.

Input Data - 

Desired Output - 

On the right side are the slicers that I'd like to use. Highlighted in green are the selected options - 

Please feel free to make any recommendations on views as well!!

Thanks,

Sailee

[dm-p · Accepted solution]

Hi @saipawar,

This can be done, but there's a little extra prep required, to give you one row per event per metric per year. I've mocked-up your data and we're going to edit the query, e.g.:

Now, select the Event Name and Metric Name columns, and then in the Transform Menu, expand the Unpivot Columns operation and then select Unpivot Other Columns, e.g.:

The reason we do this is that if additional years appear in your source data then Power Query will know to add them to the unpivot operation. After this we'll be left with a table that has the grain we want, e.g.:

(I've renamed the Value column to Year - I'd suggest you do this too).

Now we can Close & Apply and revisit our report. We can now create a matrix visual, with Metric Name on Rows, Event Name on Columns and Value in Values, e.g.:

We then just add slicers to the canvas for Event Name and Year, e.g.:

Getting it Exactly Right (as per your requirements)

Note that because everything is in the same table, your slicer won't show all values as you filter, so you might want to turn Event/Metric and Year into dimensions and relate them to your main 'fact' table (Events).

If you haven't dealt with these concepts before, this is essential reading. 

For this concept concept you can expand the query as follows:

1. In the query editor, right-click the query and choose Duplicate, e.g.:
   
2. This will create a duplicate table. We're going to rename this Event (you can call it whatever you like but I prefer plurals for fact tables and singular for dimension tables).
3. Right Click the Event Name column and select Remove Other Columns, e.g.:
   
4. This will leave only the Event Name column. We now want to remove the duplicates by right-clicking this column and selecting Remove Duplicates,. At this point, we're left with the unique values, e.g.:
   
5. Repeat steps 1-4 for the Metric Name and Year attributes so they have unique queries. Note that later you may wish to explore using a Date Table, but for now, just the Year will be fine.
6. When done, you should have something like this:
   Metric Query
   Date Query
7. Now, we can Close & Apply again.

I've now got separate tables that can filter the Events table. My model now looks like this, with relationships:

Note that I've hidden the corresponding field names in the Events table, as they are used for joining and should not be used in your visuals.

We now replace the columns, rows and slicers to use the new tables and we get something like your desired output, e.g.:

I've tried to expose as much of the field list ont he right as possible to help illustrate the changes.

Hopefully this makes sense - I've uploaded this as a workbook if you want to explore further or see more into my workings.

Good luck!

Daniel

---

If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂

[saipawar]

This is amazing!! Thanks @dm-p . You were super prompt with your response!!