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

     

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.