How to properly combine data in a table that has multiple columns tracking similar information

[M-Beans]

HI,

  Below is a screenshot of a googlesheet that is used to track uptime, loss hours, lost tons, loss reasons and sub reasons.

In order to be able to split up the loss tons in a single day and attribute to different reasons there are multiple columns of loss tons, and associated columns for the reasons and sub reasons. When trying to bring this data into a visual, I would like to be able to show the total tons for each type of loss reason ( The loss reason choices are the same for all 3 columns) , and even break it down to sub loss reason, but since there are multiple columns for Loss tons ( 1,2,3)  and Loss Reasons (1,2,3) . . I'm not sure the best way to maniputlate this table in Power BI so that it properly calculates all of the loss tons for the associated reason( Loss tons 1 --> Loss Reaons 1--> Sub Loss Reason 1) and make sure the loss tons in column "Loss Reason Tons #1" is asscoiated with "Loss Reason #1" and "Sub Loss Reason #1" and can properly add total tons together if the loss reasons are the same but in a different column. 

[M-Beans]

I didn't do a great job of explaining this:

This is the current format of how the data is showing up in Query Editor

Date Column, Loss Reason #1 Tons Column, Loss Reason #1 Column, Sub Loss Reason #1 Column,  Loss Reason #2 Tons Column, Loss Reason #2 Column, Sub Loss Reason #2 Column,  Loss Reason #3 Tons Column, Loss Reason #3 Column, Sub Loss Reason #3 Column.

The Goal is to shape the data in query editor so I have:

 Date Column, Loss Tons Column, Loss Reason Column, Sub Loss Reason Column.

When I try using the Unpivot function: I end up getting all the cell contents for all of the columns in the Value column:

If I attempt to select all the like columns like  Loss Reason #1 Tons Column,  Loss Reason #2 Tons Column,  Loss Reason #3 Tons Column:  I end up getting duplicates in the other columns that I want to unpivot.

Any steps you suggest so that I can properly pair down to 4 Columns that would have the Date, correct Tons associated with the correct loss reason, and correct sub loss reason ( if there is one)? 

Regards,

[v-yilong-msft]

Hi @M-Beans ,

Based  on your problems, here are my answers.

I think you can use Matrix visualization. It’s similar to a table but supports multiple dimensions and a stepped layout.

Also the drill through may help you too.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.