Forum Discussion

Scubadiver007's avatar
Scubadiver007
Helper I
1 year ago
Solved

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:

 

 

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

Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

3 Replies

  • Scubadiver007's avatar
    Scubadiver007
    Helper I
    1 year ago

    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

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      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.

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.