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
Hi the_kent ,
First, you need to normalise your data structure in Power Query.
Unpivot the three company columns so you end up with a company column that denotes which company the data row is for.
Then split your category column by delimiter ',' to split all of your listed categories into their own columns.
Then unpivot your category columns so you have a single category column that denotes the category for each data row.
Then group your table on Company and Category and add an aggregated field of Average of Score. You should now have a table whereby you can use the new category field as your axis, the new company field as your detail, and a SUM(table[Value]) measure as your values in a 100% stacked chart or similar.
Pete
Hey there, thanks for responding!
Again, being a newbie in this, appreciate the patience on the following queries.
Explored Unpivot in PowerBI through Transform data.
1. Is there a way to output the transformed data into a separate table? I.e. will transforming data in Unpivot re-write the base data as well?
2. Will whatever transformation I do, (i.e. Delimit, get all data in columns with individual values, and then unpivot), be repeated automatically as and when new data is entered in the original format?
Thanks again for your help!
the_kent ,
1) It depends what you mean by "re-write the base data". It will not change anything in the data source (Excel file, SQL Database etc.), but it will change the original table as it was when it loaded in Power Query. To avoid this, you can select your original table on the left of Power Query and copy/paste, or right-click and select Duplicate or Reference, then perform the transformations on this new table.
2) Yes. every step you perform in Power Query is applied between the original data being imported from the source and the resulting query being passed to the data model. The steps I've given you are all generic and reference no specific data values that can change, so no issue there. The only thing that will break it is if you make changes to any of the columns that are used in the transformations at source e.g. renaming the column at source or deleting the column at source.
Now I'm back at my desk, I've performed the steps I gave to you before and refined them a bit.
Here's the M code that you can use to see the correct steps to take.
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRikwtBpIKUOycWKLgqKMAopyAXEMDpVgdqGIjuGKEFrByIG2EpM4YyTiISpAqZyBtjKTKBEUFmtUgxaZIik1RFCIMRVKO7FIzfN5yhjo3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, #"Company 1" = _t, #"Company 2" = _t, #"Company 3" = _t, Category = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", type text}, {"Company 1", type text}, {"Company 2", type text}, {"Company 3", type text}, {"Category", type text}, {"Score", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Row", "Category", "Score"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Category", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Category.1", "Category.2"}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Row", "Score", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each ([Value] = "Yes")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute", "Value.1", "Row"}, {{"score", each List.Average([Score]), type nullable number}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Attribute", Order.Ascending}, {"Value.1", Order.Ascending}}) in #"Sorted Rows"I've left all the default columns names in so it looks exactly the same when you perform the steps, but it's easy enough to change the column names to more intuitive values later directly in the code.
This gives me the following output based on your example data:
Pete