Help with Unpivoting (and more?) to get the data format that I need...

[seaottr]

Hi all,

I'm a bit of a newbie to Power Query and although I feel like I've learned a fair bit in the past while, I really need help with this. I've spent an entire day trying to automate this (without lengthy, less dynamic workarounds), and am pretty sure this will be easy for many of you Power Query gurus out there.

I have the below (sample) raw data set in the same format:

Name	Date	Greeting	Connect	Appreciate	Comments	Greeting Max Points	Connect Max Points	Appreciate Max Points	Assessment Number
John Smith	8-22-2022​	5​	5​	0​	Comments on John	5​	5​	5​	Assessment 1
Lisa Simpson	8-24-2022​	5​	5​	2.5​	Comments on Lisa	5​	5​	5​	Assessment 1
Peter Griffin	8-23-2022​	5​	0​	5​	Comments on Peter	5​	5​	5​	Assessment 1
Joe Schmoe	8-24-2022​	5​	5​		Comments on Joe	5​	5​		Assessment 1
Mike Wzowski	8-24-2022​	5​	5​	2.5​	Comments on Mike	5​	5​	5​	Assessment 1
Peter Griffin	8-24-2022​	5​	5​	5​	Comments 2 on Peter	5​	5​	5​	Assessment 2

And need to get it in the below format (I was able to get it fairly close by unpivoting "Greeting", "Connect" and "Appreciate", but need the accurate Max Points for the respective unpivoted items beside them as the "Max Attribute Value":

Name	Date	Comments	Assessment Number	Attribute	Attribute Value	Max Attribute Value
John Smith	8-22-2022​	Comments on John	Assessment 1	Greeting	5​	5​
John Smith	8-22-2022​	Comments on John	Assessment 1	Connect	5​	5​
John Smith	8-22-2022​	Comments on John	Assessment 1	Appreciate	0​	5​
Lisa Simpson	8-24-2022​	Comments on Lisa	Assessment 1	Greeting	5​	5​
Lisa Simpson	8-24-2022​	Comments on Lisa	Assessment 1	Connect	5​	5​
Lisa Simpson	8-24-2022​	Comments on Lisa	Assessment 1	Appreciate	2.5​	5​
Peter Griffin	8-23-2022​	Comments on Peter	Assessment 1	Greeting	5​	5​
Peter Griffin	8-23-2022​	Comments on Peter	Assessment 1	Connect	0​	5​
Peter Griffin	8-23-2022​	Comments on Peter	Assessment 1	Appreciate	5​	5​
Joe Schmoe	8-24-2022​	Comments on Joe	Assessment 1	Greeting	5​	5​
Joe Schmoe	8-24-2022​	Comments on Joe	Assessment 1	Connect	5​	5​
Mike Wzowski	8-24-2022​	Comments on Mike	Assessment 1	Greeting	5​	5​
Mike Wzowski	8-24-2022​	Comments on Mike	Assessment 1	Connect	5​	5​
Mike Wzowski	8-24-2022​	Comments on Mike	Assessment 1	Appreciate	2.5​	5​
Peter Griffin	8-24-2022​	Comments 2 on Peter	Assessment 2	Greeting	5​	5​
Peter Griffin	8-24-2022​	Comments 2 on Peter	Assessment 2	Connect	5​	5​
Peter Griffin	8-24-2022​	Comments 2 on Peter	Assessment 2	Appreciate	5​	5​

Any help would be greatly appreciated!

 

[SullyWYO]

In this situation, you are trying to pivot two columns of data under one attribute. As you can see, the pivot can only grab one column of data at a time.

The trick is to combine the data that you want into one column so that when you pivot, everything goes under the header. Then you separate the data into the appropriate columns.

1. Replaced null values with zero (to remove blanks in your data set)
2. Merged "Greeting" with "Greeting Max Points"
3. Merged "Connect" with "Connect Max Points"
4. Merged "Appreciate" with "Appreciate Max Points"
5. Unpivot the new columns (Greeting, Connect, Appreciate), and call it "Attribute" & "Value"
6. Split the "Value" Column into "Attribute Value" and "Max Attribute Value"

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type text}, {"Greeting", type text}, {"Connect", type text}, {"Appreciate", type text}, {"Comments", type text}, {"Greeting Max Points", type text}, {"Connect Max Points", type text}, {"Appreciate Max Points", type text}, {"Assessment Number", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"0",Replacer.ReplaceValue,{"Greeting", "Connect", "Appreciate", "Greeting Max Points", "Connect Max Points", "Appreciate Max Points"}),
    #"Merge Greeting" = Table.CombineColumns(#"Replaced Value",{"Greeting", "Greeting Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Greeting"),
    #"Merge Connect" = Table.CombineColumns(#"Merge Greeting",{"Connect", "Connect Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Connect"),
    #"Merge Appreciate" = Table.CombineColumns(#"Merge Connect",{"Appreciate", "Appreciate Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Appreciate"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Merge Appreciate", {"Greeting", "Connect", "Appreciate"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute Value", "Max Attribute Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute Value", type text}, {"Max Attribute Value", type text}})
in
    #"Changed Type1"

 

[seaottr]

In this situation, you are trying to pivot two columns of data under one attribute. As you can see, the pivot can only grab one column of data at a time.

The trick is to combine the data that you want into one column so that when you pivot, everything goes under the header. Then you separate the data into the appropriate columns.

1. Replaced null values with zero (to remove blanks in your data set)
2. Merged "Greeting" with "Greeting Max Points"
3. Merged "Connect" with "Connect Max Points"
4. Merged "Appreciate" with "Appreciate Max Points"
5. Unpivot the new columns (Greeting, Connect, Appreciate), and call it "Attribute" & "Value"
6. Split the "Value" Column into "Attribute Value" and "Max Attribute Value"

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type text}, {"Greeting", type text}, {"Connect", type text}, {"Appreciate", type text}, {"Comments", type text}, {"Greeting Max Points", type text}, {"Connect Max Points", type text}, {"Appreciate Max Points", type text}, {"Assessment Number", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"0",Replacer.ReplaceValue,{"Greeting", "Connect", "Appreciate", "Greeting Max Points", "Connect Max Points", "Appreciate Max Points"}),
    #"Merge Greeting" = Table.CombineColumns(#"Replaced Value",{"Greeting", "Greeting Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Greeting"),
    #"Merge Connect" = Table.CombineColumns(#"Merge Greeting",{"Connect", "Connect Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Connect"),
    #"Merge Appreciate" = Table.CombineColumns(#"Merge Connect",{"Appreciate", "Appreciate Max Points"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Appreciate"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Merge Appreciate", {"Greeting", "Connect", "Appreciate"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute Value", "Max Attribute Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute Value", type text}, {"Max Attribute Value", type text}})
in
    #"Changed Type1"

Thank you so much for the quick reply SullyWYO!

I removed the replace nulls with zeros step, as I actually don't want to change the nulls since it will impact averages when I do calculations on it.

This will probably work in the interim (and is way cleaner than what I was trying to do), but was hoping to make it more dynamic in case other attributes are added later. I figured unpivoting a couple of different ways with some extra steps might achieve this, but since your way works, I will definitely use it for now!

Thank you so much again! I really appreciate it!

 

[SullyWYO]

Just keep in mind that if you have nulls and eventually add more attributes, it will screw things up when you merge the columns - some merges will have less entries than others, so when you split up the data you run the risk of it going into the wrong column.

I would replace the nulls with 0 for the pivot, then replace the 0s with null after the pivot if that's what you prefer. This will ensure that all data sets have the same number of entries and end up in the correct columns.

 

[seaottr]

Thank you so much for the quick reply SullyWYO!

I removed the replace nulls with zeros step, as I actually don't want to change the nulls since it will impact averages when I do calculations on it.

This will probably work in the interim (and is way cleaner than what I was trying to do), but was hoping to make it more dynamic in case other attributes are added later. I figured unpivoting a couple of different ways with some extra steps might achieve this, but since your way works, I will definitely use it for now!

Thank you so much again! I really appreciate it!

P.S. I'm going to leave this post as unresolved for today to see if there are any other schools of thought on ways to tackle this, but will mark as resolved if nobody else has alternate suggestions

 

[seaottr]

Just keep in mind that if you have nulls and eventually add more attributes, it will screw things up when you merge the columns - some merges will have less entries than others, so when you split up the data you run the risk of it going into the wrong column.

I would replace the nulls with 0 for the pivot, then replace the 0s with null after the pivot if that's what you prefer. This will ensure that all data sets have the same number of entries and end up in the correct columns.

But I actually need zeros for some of the calculations, so I can't remove all zeros. In my user form, Yes = 5, No = 0 and N/A = null. I need to count the zeros for the No's and need to exclude the nulls for the N/A's. I hope that makes sense. I could change the nulls to another random number (or even a letter) if you think that would help so I can easily identify/remove them afterwards.

 

[SullyWYO]

But I actually need zeros for some of the calculations, so I can't remove all zeros. In my user form, Yes = 5, No = 0 and N/A = null. I need to count the zeros for the No's and need to exclude the nulls for the N/A's. I hope that makes sense. I could change the nulls to another random number (or even a letter) if you think that would help so I can easily identify/remove them afterwards.

Sure - just use a different placeholder like 999 that you can easily convert back to null. The important thing is that you don't have empty cells, as that will screw up the pivot.

 

[JGordon11]

another way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    UnpivotColumns = Table.Unpivot(Source, {"Greeting", "Connect", "Appreciate"}, "Attribute", "Attribute Value"),
    AddMaxCol = Table.AddColumn(UnpivotColumns, "Max Attribute Value", each Record.Field(_, [Attribute] & " Max Points")),
    RemoveColumns = Table.RemoveColumns(AddMaxCol,{"Greeting Max Points", "Connect Max Points", "Appreciate Max Points"})
in
    RemoveColumns

 

[seaottr]

another way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    UnpivotColumns = Table.Unpivot(Source, {"Greeting", "Connect", "Appreciate"}, "Attribute", "Attribute Value"),
    AddMaxCol = Table.AddColumn(UnpivotColumns, "Max Attribute Value", each Record.Field(_, [Attribute] & " Max Points")),
    RemoveColumns = Table.RemoveColumns(AddMaxCol,{"Greeting Max Points", "Connect Max Points", "Appreciate Max Points"})
in
    RemoveColumns

Oooooo! I like this approach because it's more dynamic (especially if I add more attributes later)! Thank you for this suggestion JGordon11! I'll just need to make sure the "Max Points" field name matches the attribute name exactly (with the exception of " Max Points") for this to work (I have more attributes that I didn't include in the sample data and the field names don't match exactly).

Appreciate the help!!!