Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.