topic Data benchmarking query in Developer

Data benchmarking

Scubadiver007 — Wed, 05 Feb 2025 08:44:07 GMT

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):

https://explore-education-statistics.service.gov.uk/data-catalogue/data-set/48e53a9f-7129-4723-b077-94e8a6b594d0

Any thoughts on this would be appreciated.

David

Data benchmarking query

Scubadiver007 — Wed, 05 Feb 2025 14:13:19 GMT

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:

https://explore-education-statistics.service.gov.uk/data-catalogue/data-set/48e53a9f-7129-4723-b077-94e8a6b594d0

Re: Data benchmarking query

lbendlin — Wed, 05 Feb 2025 21:06:53 GMT

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.

Re: Data benchmarking query

Anonymous — Thu, 06 Feb 2025 08:16:44 GMT

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.