Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I hope I have submitted in the correct forum because I am not sure how straightforward this is
In my role, I deal with data published by the UK Government for benchmarking purposes. I would like to have a line chart comparing England, South East and our own authority. Because of the way the Dept for Education publish data, it is a bit tricky as shown:
I would like to turn it into something like this:
This is a link to an example dataset and, to directly access it in P.BI, just add "/csv" to the end of this URL (which is also on the page):
Any thoughts on this would be appreciated.
David
Hi,
I wasn't sure in which forum to post this but my gut tells me this isn't too straightforward, but then I am a novice.
As a part of my dashboard development, I am able to pull CSV files directly from the UK Dept for Education statistics catalogue.
What I would like to do is have a line chart that compares England, the South East and my local authority
but what makes it tricky is how the data is published:
Can this be done directly or would I have to import the figures into a new table with three columns for England, SE and LA?
This is the URL, and there is a URL link at the bottom which allows direct access to the data:
Hi @Scubadiver007 ,
I think you can do some transformation in Power Query Editor if you don't need so many data.
let
Source = Csv.Document(File.Contents("..."),[Delimiter=",", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"time_period", Int64.Type}, {"time_identifier", type text}, {"geographic_level", type text}, {"country_code", type text}, {"country_name", type text}, {"region_code", type text}, {"region_name", type text}, {"old_la_code", type text}, {"new_la_code", type text}, {"la_name", type text}, {"lad_code", type text}, {"lad_name", type text}, {"breakdown_topic", type text}, {"breakdown", type text}, {"sex", type text}, {"children_number", Int64.Type}, {"average_elgs", type number}, {"elg_number", Int64.Type}, {"elg_percentage", type number}, {"comm_lang_lit_number", Int64.Type}, {"comm_lang_lit_percentage", type number}, {"gld_number", Int64.Type}, {"gld_percentage", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([breakdown_topic] = "Total") and ([sex] = "Total") and ([country_name] = "England") and ([region_name] = "" or [region_name] = "South East") and ([la_name] = "" or [la_name] = "Wokingham") and ([geographic_level] <> "Local authority district")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"region_name", Order.Ascending}, {"la_name", Order.Ascending}, {"gld_percentage", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"country_code", "region_code", "old_la_code", "new_la_code", "lad_code", "lad_name", "children_number", "average_elgs", "elg_number", "elg_percentage", "comm_lang_lit_number", "comm_lang_lit_percentage", "gld_number"})
in
#"Removed Columns"
Copy your data source position and paste it in ... in above code.
Then create a Legend Table for calculation.
Legend =
DATATABLE(
"Legend",STRING,
"Order",INTEGER,
{
{"Wokingham",1},
{"South East",2},
{"England",3}
}
)
Measure:
MEASURE =
SWITCH (
SELECTEDVALUE ( Legend[Legend] ),
"Wokingham",
CALCULATE (
SUM ( '1_eyfsp_headline_measures_2022_2024'[gld_percentage] ),
'1_eyfsp_headline_measures_2022_2024'[la_name] = "Wokingham"
),
"South East",
CALCULATE (
SUM ( '1_eyfsp_headline_measures_2022_2024'[gld_percentage] ),
'1_eyfsp_headline_measures_2022_2024'[region_name] = "South East"
&& '1_eyfsp_headline_measures_2022_2024'[la_name] = BLANK ()
),
"England",
CALCULATE (
SUM ( '1_eyfsp_headline_measures_2022_2024'[gld_percentage] ),
'1_eyfsp_headline_measures_2022_2024'[country_name] = "England"
&& '1_eyfsp_headline_measures_2022_2024'[region_name] = BLANK ()
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
should we be worried about these data quality issues?
What I would like to do is have a line chart that compares England, the South East and my local authority
That is a design red flag. You are repeating the same data in the same visual. Make sure your users understand that.
User | Count |
---|---|
3 | |
3 | |
2 | |
2 | |
2 |
User | Count |
---|---|
10 | |
4 | |
4 | |
3 | |
3 |