Forum Discussion
Creating a Stacked Bar Chart with data from Multiple Columns AND a column with Multiple Values
the_kent ,
No worries. I'm guessing your score values went through the roof when you tried to sum them as you've not reaggregated the data into unique row sets.
This is why in my steps I had the Group By step at the end. This reaggregates on an average basis (not sum!) the duplicated rows that were created by the unpivot steps. Average works here as we know we are only grouping duplicated rows, so the average of them will just be the original value that was duplicated.
Unpivoting once isn't a problem as the data is recategorised correctly into a normalised structure. It's when we unpivot twice (due to two lots of different dimensions needing to be normalised) that we then get erroneous duplications. We are essentially creating a crossjoin on the second unpivot against each of the previously normalised values from the first unpivot.
TL;DR - You have to do the Group By step to reaggregate your scores correctly.
*EDIT* I ws a bit confused reading where you say that I'd included [score] in my unpivot columns so I rechecked it and it appears this way because Power Query has actually done a code switch. I used the Unpivot function from the GUI which you would expect to produce the function Table.UnpivotColumns(theColumnsYouHadSelected). However, it's actually turned it on its head an created the M code as Table.UnpivotOtherColumns(theColumnsYouDidn'tHaveSelected). A bit confusing, to be sure, but correct in terms of output.
Pete
the_kent ,
No worries. I'm guessing your score values went through the roof when you tried to sum them as you've not reaggregated the data into unique row sets.
This is why in my steps I had the Group By step at the end. This reaggregates on an average basis (not sum!) the duplicated rows that were created by the unpivot steps. Average works here as we know we are only grouping duplicated rows, so the average of them will just be the original value that was duplicated.
Unpivoting once isn't a problem as the data is recategorised correctly into a normalised structure. It's when we unpivot twice (due to two lots of different dimensions needing to be normalised) that we then get erroneous duplications. We are essentially creating a crossjoin on the second unpivot against each of the previously normalised values from the first unpivot.
TL;DR - You have to do the Group By step to reaggregate your scores correctly.
*EDIT* I ws a bit confused reading where you say that I'd included [score] in my unpivot columns so I rechecked it and it appears this way because Power Query has actually done a code switch. I used the Unpivot function from the GUI which you would expect to produce the function Table.UnpivotColumns(theColumnsYouHadSelected). However, it's actually turned it on its head an created the M code as Table.UnpivotOtherColumns(theColumnsYouDidn'tHaveSelected). A bit confusing, to be sure, but correct in terms of output.
Pete
Gotcha! Will try it out!
Edit: It was the other way around in terms of data mismatch - I got lesser total values.
Odd because the Sum by Category was on-point.
I also realised that some values in 'Category' were blank - this could be the issue I think - anyways shall be away tinkering and try that code as well!