Clustered column chart and grouping data from a tabular set

[dougloader]

Hi there,

I am creating a visual of student exam results that students sat in Year 7 and again in Year 9. My data set is a tabular format so each student in the school has: "name/current year group/yr7spellingresult/yr7readingresult/yr9spellingresult/yr9readingresult" etc, all in 1 row.

I have illustrated this below in the image from PBI Desktop. You can see the table looks good, but my chart cannot distinguish between the exam results i.e. Yr 7 group and Yr 9 group. I would prefer that the axis stated "Abby L Y7" and "Abby L Y9", with some spacing between the 2 groups of 5 colums. That way teachers could easily see the imrpovements being made by selected student.

I have created this Column Chart before in Google Sheets as per the below. But I used some basic Vlookups to create the table before I created the chart where I could specify the fields that I was referencing.

I hope that this is clear and that someone can assist.

Thanks in advance.

[v-yuezhe-msft · Accepted solution]

@dougloader,

You can perform these steps(Unpivoted Columns, Split Column, Pivoted Column) in Query Editor of Power BI Desktop to get your expected table, theses steps will generate the following bold codes in Advanced Editor, you can directly copy the code into the Advanced Editor of your query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwrDoAwEIThqzSrK1qy04dE4OAETU0JAovj9nQH82ey2XytyTrG9bpdvKDAWpMV/47chTvMJmRrsJ8UF7vnIN032Z77dMc8aMlstZJRMkpGyYAMyICMGtM/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"First Last" = _t, #"Y7 GP Score" = _t, #"Y7 Numeracy Score" = _t, #"Y7 Reading Score" = _t, #"Y7 Spelling Score" = _t, #"Y7 Writing Score" = _t, #"Y9 GP Score" = _t, #"Y9 Numeracy Score" = _t, #"Y9 Reading Score" = _t, #"Y9 Spelling Score" = _t, #"Y9 Writing Score" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Y9 Writing Score", Int64.Type}, {"Y9 Spelling Score", Int64.Type}, {"Y9 Reading Score", Int64.Type}, {"Y9 Numeracy Score", Int64.Type}, {"Y9 GP Score", Int64.Type}, {"Y7 Writing Score", Int64.Type}, {"Y7 Spelling Score", Int64.Type}, {"Y7 Reading Score", Int64.Type}, {"Y7 Numeracy Score", Int64.Type}, {"Y7 GP Score", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"First Last"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"First Last", type text}, {"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

Regards,
Lydia

[dougloader]

@v-yuezhe-msft

Thank you for taking the time to respond, it has been a huge help.

After many hours of reviewing my data, I realised that it was simply presented incorrectly. Your solution was correct, I needed to un-pivot my data. But due to the "Year 7/Year 9" tag not being included in the my rows, I had to add that as well.

Thank you for leading me in the right direction.

One last question though: Why does the Clustered Column chart have so much white space on either side of the values? In the example that you provided it is the same. Can this be reduced somehow?

Thanks