Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Scubadiver007
New Member

Data benchmarking

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:

 

Scubadiver007_0-1738744735238.png

 

I would like to turn it into something like this:

 

Scubadiver007_1-1738744891976.png

 

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-...

 

Any thoughts on this would be appreciated.

 

David

3 REPLIES 3
Scubadiver007
New Member

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

 

Scubadiver007_1-1738764495422.png

 

 

 

but what makes it tricky is how the data is published:

 

Scubadiver007_0-1738764437189.png

 

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-...

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.

vrzhoumsft_0-1738828485482.png

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.

vrzhoumsft_1-1738829772532.png

 

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?

 

lbendlin_0-1738787848802.png

 

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.

lbendlin_1-1738789590053.png

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.