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
You're a star to take time out of your day for a detailed response, thank you so much!
1) Yes, I figured out the duplication thing immediately after posting here, that worked as needed! And just re-did the reports.
2) Got it, and I see it in my data as well
While I'll take time to digest the code, just want to quickly run you through my Unpivot logic if you've time (else no worries, I'll do some Trial and Error)
An issue I faced was the score sums against the categories post Unpviot as against the original data was not matching. I'm guessing this is because I did not include the 'score' as part of both phases of unpivoting.
My understanding of Unpivot was that columns not selected would just be replicated against the Columns being unpivoted - but I see in your code that it is part of the unpivot as well.
Here's what I did
- Unpivoted the companies (without selecting the score) -
- Used text to columns with a delimiter to separate the categories
- Unpivoted the categories (without selecting the score)
Having done this, I got the reports I wanted in a much better fashion (even other reports where previously to summarise company wise scores I had put a quick measure on the Score with filter on Company Column = Yes)
BUT the score totals went for a toss
Again - on this mismatch I'll run some trial and error soon in my own time - but if you have the time, do help refine my understanding of the unpivot logic 🙂
Thanks so much!
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!