cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dougloader Frequent Visitor
Frequent Visitor

Clustered column chart and grouping data from a tabular set

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.

 

Screen Shot 2017-08-22 at 9.43.39 pm.png

 

 

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.

 

Screen Shot 2017-08-22 at 9.42.31 pm.png

 

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

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Clustered column chart and grouping data from a tabular set

@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"


1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Clustered column chart and grouping data from a tabular set

@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"


1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

dougloader Frequent Visitor
Frequent Visitor

Re: Clustered column chart and grouping data from a tabular set

@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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 108 members 1,576 guests
Please welcome our newest community members: