Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I'm trying to create a simple bar chart that measures the % of my test scores for each month
This is the format i want to be able to show in PowerBI (I created the below in excel )
This is how I have the data pullled into powerbi from a sharepoint site
This is as far as I have gotten :
not sure what to do next so any guidance would be appreciated.
I think it might be down to the way i have the data entered on the table but im not sure.
Any advice would be appreciated.
Solved! Go to Solution.
Hi @PowerBINoob12 ,
Approve with @parry2k .
Please try:
First, Unpivot the other columns:
Output:
Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTI0MMBJxupEK7mlJhURqdQ3sSg5gwh1jgVFmTlEmUeMrV6lealYFViiqsohxjDH0vTS4hLCxgWnFpSk5ialFhFhpn9yST5xKv3yy4g11CU1GabUAo/KWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Maths = _t, English = _t, Spanish = _t, French = _t, Latin = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Maths", Int64.Type}, {"English", Int64.Type}, {"Spanish", Int64.Type}, {"French", Int64.Type}, {"Latin", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Then apply it to the visual:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PowerBINoob12 ,
Approve with @parry2k .
Please try:
First, Unpivot the other columns:
Output:
Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTI0MMBJxupEK7mlJhURqdQ3sSg5gwh1jgVFmTlEmUeMrV6lealYFViiqsohxjDH0vTS4hLCxgWnFpSk5ialFhFhpn9yST5xKv3yy4g11CU1GabUAo/KWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Maths = _t, English = _t, Spanish = _t, French = _t, Latin = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Maths", Int64.Type}, {"English", Int64.Type}, {"Spanish", Int64.Type}, {"French", Int64.Type}, {"Latin", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Then apply it to the visual:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@PowerBINoob12 you need to unpivot your data and then it should be easy:
Transform Data -> Select Month Column -> Right CLick -> Unpivot other column
It will add two new column Attribute and Value, rename these columns as you see fit. Close And Apply
In a visual, put attribute on x-axis, month on legend and count on values (y-Axis) and that will do it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for the guidance so far but I think there may be an issue with what is populated in attribute now?
It also wont let me add more than one item to the Y-Axis:
Any ideas?
Thanks.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |