Data in the wrong format?

[jgreen82]

Hi everyone, I don't know if I'm barking up the wrong tree or not. 

I'd like to show trends for particular focus groups in our school. I've organised the data in a way that I *think* is acceptible. But can't find tutorials or methods to show it how I'd like. A slicer for Focus group, and year then graphs to show the results in each of the subjects over time is what I'm after but I can't get there with my beginner knowledge! 

Not sure if this link works, but have you got any suggestions? Is the format of my data source my first stumbling block? 

Link to spreadsheet 

[jgreen82]

Thank you so much for taking the time to help me out. I'll have a look tonight to see if I can transform my own data. 

[lbendlin · Accepted solution]

 Is the format of my data source my first stumbling block? 

Absolutely.  That format is not usable. First order of business is to unpivot it and then to combine all sheets

let
    Source = Excel.Workbook(File.Contents("C:\Users\Lutz\Downloads\Trends - PowerBi .xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Hidden] = false)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.UnpivotOtherColumns(Table.PromoteHeaders([Data], [PromoteAllScalars=true]), {"Focus Group"}, "Attribute", "Value")),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Focus Group", "Attribute", "Value"}, {"Focus Group", "Attribute", "Value"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Item", "Focus Group", "Attribute", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Focus Group", type text}, {"Attribute", type text}, {"Value", type number}})
in
    #"Changed Type"

Now it is usable in Power BI and you can create whatever visuals you require.