SOLVED

How to use a PivotTable with unique columns?

Copper Contributor

Hey, I'm trying to separate three columns of data by 9 different possible rankings in a PivotTable. Right now the columns are being combined together into the first one, leading to them all having the same values as the first column. It looks like this:

 

excelhelp.png

 

I've tried lots of different things with the table and this is the closest I've gotten to get unique values. As you can see, the 2nd and 3rd rows are copying the 1st row's data. They should be unique to their own respective column's data, but I'm unsure of how to do that (or if it's even possible with this setup?).

 

Thanks if you can help with this,

14 Replies
Please upload a sample of your data.
Away from home right now, but basically it's a Google Form response sheet where each column = 1 response. Each response can select one of the ranks (Unkranked, Bronze, etc.) for each of the three roles (Tank, Damage, Support) - each being a separate column. This PivotTable is only using the Tank's response column as it's the first one, I'm trying to make it use all 3 roles' so that in that picture not all the numbers are the same for each Role.

I'll try to upload it when I'm home, but I feel like I'm just missing something basic here or misusing the Table that someone should be able to point out.

@JoeDon16 

Okay. Am including a quick-and-dirty example that achieves what I believe you are trying to do. If not, try   uploading a sample of your data at your earliest convenience.

@Riny_van_Eekelen 

Thanks for making that! I appreciate it, but it's not quite what I'm looking for.

 

Here's an example of what I'm dealing with, in the PivotTable sheet you can see the exact table I'm trying to work with. I'm not sure what to put in the Columns section if it can only handle one column at a time from my Responses sheet.

@JoeDon16 

 

Got it! Your data is already in a table format. Then, to my knowledge, you can't use a pivot tables to rearrange the date. Use COUNTIF instead. Added the formulae to your sheet. Would this work for you?

@JoeDon16 

As @Riny_van_Eekelen pointed out your data is already in a cross tabular form.

You have to unpivot first. The easiest way is with Power Query.

Then you can load the data into a Pivot Table.

Yes! Thank you very much, that works perfectly! :)

Kind of sucks that it's not possible to use a PivotTable to achieve the same result, but oh well. I do have one last question, for setting up a COUNTIF like this table, is there any easy way to do it if I had let's say 100 ranks? Or just a quick way to set it up. Right now I only really know how to manually type in the COUNTIF command and copy/paste the cells around, I never have really used it before.
Ah alright, I guess I'll look into that too. I heard a little bit about it when I was searching this question up earlier, but I didn't think it would help! Thank you.
best response confirmed by JoeDon16 (Copper Contributor)
Solution

@JoeDon16 

 

Since your data on Responses Tab was already in pivoted form, I used Power Query to transform and UnPivot the data on Responses Tab to create a Pivot Table on a New Sheet (Green Tab).

To do that, I converted the data on Responses Tab into an Excel Table and named it "Responses", loaded the data into the Power Query, removed the TimeStamp column as it was not required in the Pivot Table to be created, UnPivoted all the columns, renamed the columns and loaded as a Pivot Table Report on a New Sheet.

 

See if this is what you were trying to achieve.

 

 

@JoeDon16 

You can expand the table I created by inserting columns. Then just drag the formulas across. Just try it. It's a good way to learn new things :)

 

If you really want to use PivotTable, you need to "unpivot" your data as suggested by @Detlef Lewin.

Thank you for explaining that! I'll try to recreate what you did in that file, thank you very much for sharing it :)

@JoeDon16 

You're welcome!

 

If you convert your data on Responses Tab into an Excel Table and name it "Responses", you can create a blank query and in the Advanced Editor, you may paste the following query and load it as Pivot Table Report on a New Sheet.

Just remember to change the name of the Table on Responses Tab in the first line of the query below...

Source = Excel.CurrentWorkbook(){[Name="Responses"]}[Content],

 

let
    Source = Excel.CurrentWorkbook(){[Name="Responses"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"What is Your Current Rank on Tank Role?", type text}, {"What is Your Current Rank on Damage Role?", type text}, {"What is Your Current Rank on Support Role?", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Timestamp"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Roles"}, {"Value", "Rank"}})
in
    #"Renamed Columns"

 

Beautiful!

 

Alright, thank you to everyone who helped me out here! I've got all my Pivots working and now have a little bit of a better understanding of Power Query. Going to finally go to sleep now, thanks again guys!

@JoeDon16 

You're welcome! Glad we could help.

 

Good night!

1 best response

Accepted Solutions
best response confirmed by JoeDon16 (Copper Contributor)
Solution

@JoeDon16 

 

Since your data on Responses Tab was already in pivoted form, I used Power Query to transform and UnPivot the data on Responses Tab to create a Pivot Table on a New Sheet (Green Tab).

To do that, I converted the data on Responses Tab into an Excel Table and named it "Responses", loaded the data into the Power Query, removed the TimeStamp column as it was not required in the Pivot Table to be created, UnPivoted all the columns, renamed the columns and loaded as a Pivot Table Report on a New Sheet.

 

See if this is what you were trying to achieve.

 

 

View solution in original post