Any idea on how I group the data by years?

[Bluebird1927rw]

https://www.ons.gov.uk/file?uri=/pe...erencetables/2018/conceptions2018workbook.xls

I’m working on table 6 in the workbook above. I’m trying to create a pivot table to compare the trends on the key metrics over the years shown. E.g a trend line from 1999 to 2018. How can I better incorporate the different years into the table to extract each year easily to compare and analyse in a pivot table ?

 

[Misca]

Welcome to the board!

You're going to have to unpivot the table first and you're lucky to have Power Query there to help you.

Open an empty Excel workbook and select Get Data / From Other Sources / Blank Query from the Data group of your Excel ribbon. Power Query editor opens. Select Advanced Editor and replace the existing code with this one:

let
    Source = Excel.Workbook(Web.Contents("https://www.ons.gov.uk/file?uri=/peoplepopulationandcommunity/birthsdeathsandmarriages/conceptionandfertilityrates/datasets/conceptionstatisticsenglandandwalesreferencetables/2018/conceptions2018workbook.xls"), null, true),
    #"Table 1" = Source{[Name="Table 6"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Table 1",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column3", "Column4", "Column5"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Removed Columns", {{"Column1", each Text.Start(_, 4), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Area#(tab)Code1", type text}, {"Area#(tab)Name", type text}, {"Area#(tab)Geography", type text}, {"2018#(tab)Number of Conceptions", Int64.Type}, {"2018#(tab)Conception rate per 1,000 women in age group", type number}, {"2018#(tab)Maternity rate per 1,000 women in age group", type number}, {"2018#(tab)Abortion rate per 1,000 women in age group", type number}, {"2018#(tab)Percentage of conceptions leading to abortion", type number}, {"2017#(tab)Number of Conceptions", Int64.Type}, {"2017#(tab)Conception rate per 1,000 women in age group", type number}, {"2017#(tab)Maternity rate per 1,000 women in age group", type number}, {"2017#(tab)Abortion rate per 1,000 women in age group", type number}, {"2017#(tab)Percentage of conceptions leading to abortion", type number}, {"2016#(tab)Number of Conceptions", Int64.Type}, {"2016#(tab)Conception rate per 1,000 women in age group", type number}, {"2016#(tab)Maternity rate per 1,000 women in age group", type number}, {"2016#(tab)Abortion rate per 1,000 women in age group", type number}, {"2016#(tab)Percentage of conceptions leading to abortion", type number}, {"2015#(tab)Number of Conceptions", Int64.Type}, {"2015#(tab)Conception rate per 1,000 women in age group", type number}, {"2015#(tab)Maternity rate per 1,000 women in age group", type number}, {"2015#(tab)Abortion rate per 1,000 women in age group", type number}, {"2015#(tab)Percentage of conceptions leading to abortion", type number}, {"2014#(tab)Number of Conceptions", Int64.Type}, {"2014#(tab)Conception rate per 1,000 women in age group", type number}, {"2014#(tab)Maternity rate per 1,000 women in age group", type number}, {"2014#(tab)Abortion rate per 1,000 women in age group", type number}, {"2014#(tab)Percentage of conceptions leading to abortion", type number}, {"2013#(tab)Number of Conceptions", Int64.Type}, {"2013#(tab)Conception rate per 1,000 women in age group", type number}, {"2013#(tab)Maternity rate per 1,000 women in age group", type number}, {"2013#(tab)Abortion rate per 1,000 women in age group", type number}, {"2013#(tab)Percentage of conceptions leading to abortion", type number}, {"2012#(tab)Number of Conceptions", Int64.Type}, {"2012#(tab)Conception rate per 1,000 women in age group", type number}, {"2012#(tab)Maternity rate per 1,000 women in age group", type number}, {"2012#(tab)Abortion rate per 1,000 women in age group", type number}, {"2012#(tab)Percentage of conceptions leading to abortion", type number}, {"2011#(tab)Number of Conceptions", Int64.Type}, {"2011#(tab)Conception rate per 1,000 women in age group", type number}, {"2011#(tab)Maternity rate per 1,000 women in age group", type number}, {"2011#(tab)Abortion rate per 1,000 women in age group", type number}, {"2011#(tab)Percentage of conceptions leading to abortion", type number}, {"2010#(tab)Number of Conceptions", Int64.Type}, {"2010#(tab)Conception rate per 1,000 women in age group", type number}, {"2010#(tab)Maternity rate per 1,000 women in age group", type number}, {"2010#(tab)Abortion rate per 1,000 women in age group", type number}, {"2010#(tab)Percentage of conceptions leading to abortion", type number}, {"2009#(tab)Number of Conceptions", Int64.Type}, {"2009#(tab)Conception rate per 1,000 women in age group", type number}, {"2009#(tab)Maternity rate per 1,000 women in age group", type number}, {"2009#(tab)Abortion rate per 1,000 women in age group", type number}, {"2009#(tab)Percentage of conceptions leading to abortion", type number}, {"2008#(tab)Number of Conceptions", Int64.Type}, {"2008#(tab)Conception rate per 1,000 women in age group", type number}, {"2008#(tab)Maternity rate per 1,000 women in age group", type number}, {"2008#(tab)Abortion rate per 1,000 women in age group", type number}, {"2008#(tab)Percentage of conceptions leading to abortion", type number}, {"2007#(tab)Number of Conceptions", Int64.Type}, {"2007#(tab)Conception rate per 1,000 women in age group", type number}, {"2007#(tab)Maternity rate per 1,000 women in age group", type number}, {"2007#(tab)Abortion rate per 1,000 women in age group", type number}, {"2007#(tab)Percentage of conceptions leading to abortion", type number}, {"2006#(tab)Number of Conceptions", Int64.Type}, {"2006#(tab)Conception rate per 1,000 women in age group", type number}, {"2006#(tab)Maternity rate per 1,000 women in age group", type number}, {"2006#(tab)Abortion rate per 1,000 women in age group", type number}, {"2006#(tab)Percentage of conceptions leading to abortion", type number}, {"2005#(tab)Number of Conceptions", Int64.Type}, {"2005#(tab)Conception rate per 1,000 women in age group", type number}, {"2005#(tab)Maternity rate per 1,000 women in age group", type number}, {"2005#(tab)Abortion rate per 1,000 women in age group", type number}, {"2005#(tab)Percentage of conceptions leading to abortion", type number}, {"2004#(tab)Number of Conceptions", Int64.Type}, {"2004#(tab)Conception rate per 1,000 women in age group", type number}, {"2004#(tab)Maternity rate per 1,000 women in age group", type number}, {"2004#(tab)Abortion rate per 1,000 women in age group", type number}, {"2004#(tab)Percentage of conceptions leading to abortion", type number}, {"2003#(tab)Number of Conceptions", Int64.Type}, {"2003#(tab)Conception rate per 1,000 women in age group", type number}, {"2003#(tab)Maternity rate per 1,000 women in age group", type number}, {"2003#(tab)Abortion rate per 1,000 women in age group", type number}, {"2003#(tab)Percentage of conceptions leading to abortion", type number}, {"2002#(tab)Number of Conceptions", Int64.Type}, {"2002#(tab)Conception rate per 1,000 women in age group", type number}, {"2002#(tab)Maternity rate per 1,000 women in age group", type number}, {"2002#(tab)Abortion rate per 1,000 women in age group", type number}, {"2002#(tab)Percentage of conceptions leading to abortion", type number}, {"2001#(tab)Number of Conceptions", Int64.Type}, {"2001#(tab)Conception rate per 1,000 women in age group", type number}, {"2001#(tab)Maternity rate per 1,000 women in age group", type number}, {"2001#(tab)Abortion rate per 1,000 women in age group", type number}, {"2001#(tab)Percentage of conceptions leading to abortion", type number}, {"2000#(tab)Number of Conceptions", Int64.Type}, {"2000#(tab)Conception rate per 1,000 women in age group", type number}, {"2000#(tab)Maternity rate per 1,000 women in age group", type number}, {"2000#(tab)Abortion rate per 1,000 women in age group", type number}, {"2000#(tab)Percentage of conceptions leading to abortion", type number}, {"1999#(tab)Number of Conceptions", Int64.Type}, {"1999#(tab)Conception rate per 1,000 women in age group", type number}, {"1999#(tab)Maternity rate per 1,000 women in age group", type number}, {"1999#(tab)Abortion rate per 1,000 women in age group", type number}, {"1999#(tab)Percentage of conceptions leading to abortion", type number}, {"1998#(tab)Number of Conceptions", Int64.Type}, {"1998#(tab)Conception rate per 1,000 women in age group", type number}, {"1998#(tab)Maternity rate per 1,000 women in age group", type number}, {"1998#(tab)Abortion rate per 1,000 women in age group", type number}, {"1998#(tab)Percentage of conceptions leading to abortion", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Area#(tab)Code1", "Area#(tab)Name", "Area#(tab)Geography"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Year", "Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year", Int64.Type}, {"Attribute", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute]), "Attribute", "Value"),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Pivoted Column", {"Conception rate per 1,000 women in age group"})
in
    #"Removed Errors"

The code gets data directly from the linked file and unpivots it for you.

The code gets you only half way there, though: There's still a lot of Total / Sub Total rows in the data that you might want to filter out before the data is really usable. I'd use the Area Geography column to filter out all the ones you don't want. Do this in Power Query as well. When you're done you can load your data to a worksheet or to your Data Model if you want to create your pivot table using the Power Pivot.

 

[Bluebird1927rw]

Welcome to the board!

You're going to have to unpivot the table first and you're lucky to have Power Query there to help you.

Open an empty Excel workbook and select Get Data / From Other Sources / Blank Query from the Data group of your Excel ribbon. Power Query editor opens. Select Advanced Editor and replace the existing code with this one:

let
    Source = Excel.Workbook(Web.Contents("https://www.ons.gov.uk/file?uri=/peoplepopulationandcommunity/birthsdeathsandmarriages/conceptionandfertilityrates/datasets/conceptionstatisticsenglandandwalesreferencetables/2018/conceptions2018workbook.xls"), null, true),
    #"Table 1" = Source{[Name="Table 6"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Table 1",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column3", "Column4", "Column5"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Removed Columns", {{"Column1", each Text.Start(_, 4), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Area#(tab)Code1", type text}, {"Area#(tab)Name", type text}, {"Area#(tab)Geography", type text}, {"2018#(tab)Number of Conceptions", Int64.Type}, {"2018#(tab)Conception rate per 1,000 women in age group", type number}, {"2018#(tab)Maternity rate per 1,000 women in age group", type number}, {"2018#(tab)Abortion rate per 1,000 women in age group", type number}, {"2018#(tab)Percentage of conceptions leading to abortion", type number}, {"2017#(tab)Number of Conceptions", Int64.Type}, {"2017#(tab)Conception rate per 1,000 women in age group", type number}, {"2017#(tab)Maternity rate per 1,000 women in age group", type number}, {"2017#(tab)Abortion rate per 1,000 women in age group", type number}, {"2017#(tab)Percentage of conceptions leading to abortion", type number}, {"2016#(tab)Number of Conceptions", Int64.Type}, {"2016#(tab)Conception rate per 1,000 women in age group", type number}, {"2016#(tab)Maternity rate per 1,000 women in age group", type number}, {"2016#(tab)Abortion rate per 1,000 women in age group", type number}, {"2016#(tab)Percentage of conceptions leading to abortion", type number}, {"2015#(tab)Number of Conceptions", Int64.Type}, {"2015#(tab)Conception rate per 1,000 women in age group", type number}, {"2015#(tab)Maternity rate per 1,000 women in age group", type number}, {"2015#(tab)Abortion rate per 1,000 women in age group", type number}, {"2015#(tab)Percentage of conceptions leading to abortion", type number}, {"2014#(tab)Number of Conceptions", Int64.Type}, {"2014#(tab)Conception rate per 1,000 women in age group", type number}, {"2014#(tab)Maternity rate per 1,000 women in age group", type number}, {"2014#(tab)Abortion rate per 1,000 women in age group", type number}, {"2014#(tab)Percentage of conceptions leading to abortion", type number}, {"2013#(tab)Number of Conceptions", Int64.Type}, {"2013#(tab)Conception rate per 1,000 women in age group", type number}, {"2013#(tab)Maternity rate per 1,000 women in age group", type number}, {"2013#(tab)Abortion rate per 1,000 women in age group", type number}, {"2013#(tab)Percentage of conceptions leading to abortion", type number}, {"2012#(tab)Number of Conceptions", Int64.Type}, {"2012#(tab)Conception rate per 1,000 women in age group", type number}, {"2012#(tab)Maternity rate per 1,000 women in age group", type number}, {"2012#(tab)Abortion rate per 1,000 women in age group", type number}, {"2012#(tab)Percentage of conceptions leading to abortion", type number}, {"2011#(tab)Number of Conceptions", Int64.Type}, {"2011#(tab)Conception rate per 1,000 women in age group", type number}, {"2011#(tab)Maternity rate per 1,000 women in age group", type number}, {"2011#(tab)Abortion rate per 1,000 women in age group", type number}, {"2011#(tab)Percentage of conceptions leading to abortion", type number}, {"2010#(tab)Number of Conceptions", Int64.Type}, {"2010#(tab)Conception rate per 1,000 women in age group", type number}, {"2010#(tab)Maternity rate per 1,000 women in age group", type number}, {"2010#(tab)Abortion rate per 1,000 women in age group", type number}, {"2010#(tab)Percentage of conceptions leading to abortion", type number}, {"2009#(tab)Number of Conceptions", Int64.Type}, {"2009#(tab)Conception rate per 1,000 women in age group", type number}, {"2009#(tab)Maternity rate per 1,000 women in age group", type number}, {"2009#(tab)Abortion rate per 1,000 women in age group", type number}, {"2009#(tab)Percentage of conceptions leading to abortion", type number}, {"2008#(tab)Number of Conceptions", Int64.Type}, {"2008#(tab)Conception rate per 1,000 women in age group", type number}, {"2008#(tab)Maternity rate per 1,000 women in age group", type number}, {"2008#(tab)Abortion rate per 1,000 women in age group", type number}, {"2008#(tab)Percentage of conceptions leading to abortion", type number}, {"2007#(tab)Number of Conceptions", Int64.Type}, {"2007#(tab)Conception rate per 1,000 women in age group", type number}, {"2007#(tab)Maternity rate per 1,000 women in age group", type number}, {"2007#(tab)Abortion rate per 1,000 women in age group", type number}, {"2007#(tab)Percentage of conceptions leading to abortion", type number}, {"2006#(tab)Number of Conceptions", Int64.Type}, {"2006#(tab)Conception rate per 1,000 women in age group", type number}, {"2006#(tab)Maternity rate per 1,000 women in age group", type number}, {"2006#(tab)Abortion rate per 1,000 women in age group", type number}, {"2006#(tab)Percentage of conceptions leading to abortion", type number}, {"2005#(tab)Number of Conceptions", Int64.Type}, {"2005#(tab)Conception rate per 1,000 women in age group", type number}, {"2005#(tab)Maternity rate per 1,000 women in age group", type number}, {"2005#(tab)Abortion rate per 1,000 women in age group", type number}, {"2005#(tab)Percentage of conceptions leading to abortion", type number}, {"2004#(tab)Number of Conceptions", Int64.Type}, {"2004#(tab)Conception rate per 1,000 women in age group", type number}, {"2004#(tab)Maternity rate per 1,000 women in age group", type number}, {"2004#(tab)Abortion rate per 1,000 women in age group", type number}, {"2004#(tab)Percentage of conceptions leading to abortion", type number}, {"2003#(tab)Number of Conceptions", Int64.Type}, {"2003#(tab)Conception rate per 1,000 women in age group", type number}, {"2003#(tab)Maternity rate per 1,000 women in age group", type number}, {"2003#(tab)Abortion rate per 1,000 women in age group", type number}, {"2003#(tab)Percentage of conceptions leading to abortion", type number}, {"2002#(tab)Number of Conceptions", Int64.Type}, {"2002#(tab)Conception rate per 1,000 women in age group", type number}, {"2002#(tab)Maternity rate per 1,000 women in age group", type number}, {"2002#(tab)Abortion rate per 1,000 women in age group", type number}, {"2002#(tab)Percentage of conceptions leading to abortion", type number}, {"2001#(tab)Number of Conceptions", Int64.Type}, {"2001#(tab)Conception rate per 1,000 women in age group", type number}, {"2001#(tab)Maternity rate per 1,000 women in age group", type number}, {"2001#(tab)Abortion rate per 1,000 women in age group", type number}, {"2001#(tab)Percentage of conceptions leading to abortion", type number}, {"2000#(tab)Number of Conceptions", Int64.Type}, {"2000#(tab)Conception rate per 1,000 women in age group", type number}, {"2000#(tab)Maternity rate per 1,000 women in age group", type number}, {"2000#(tab)Abortion rate per 1,000 women in age group", type number}, {"2000#(tab)Percentage of conceptions leading to abortion", type number}, {"1999#(tab)Number of Conceptions", Int64.Type}, {"1999#(tab)Conception rate per 1,000 women in age group", type number}, {"1999#(tab)Maternity rate per 1,000 women in age group", type number}, {"1999#(tab)Abortion rate per 1,000 women in age group", type number}, {"1999#(tab)Percentage of conceptions leading to abortion", type number}, {"1998#(tab)Number of Conceptions", Int64.Type}, {"1998#(tab)Conception rate per 1,000 women in age group", type number}, {"1998#(tab)Maternity rate per 1,000 women in age group", type number}, {"1998#(tab)Abortion rate per 1,000 women in age group", type number}, {"1998#(tab)Percentage of conceptions leading to abortion", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Area#(tab)Code1", "Area#(tab)Name", "Area#(tab)Geography"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Year", "Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year", Int64.Type}, {"Attribute", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute]), "Attribute", "Value"),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Pivoted Column", {"Conception rate per 1,000 women in age group"})
in
    #"Removed Errors"

The code gets data directly from the linked file and unpivots it for you.

The code gets you only half way there, though: There's still a lot of Total / Sub Total rows in the data that you might want to filter out before the data is really usable. I'd use the Area Geography column to filter out all the ones you don't want. Do this in Power Query as well. When you're done you can load your data to a worksheet or to your Data Model if you want to create your pivot table using the Power Pivot.

@Misca That's incredibly helpful, thank you very much for taking your time to help with this. I get 2 errors with the code, firstly under the 'remove' errors step i'm getting 'We couldn't convert to number'. Second error is the list is incomplete, it seems that the query is running on data up to a 211 rows and is missing some of the geographical regions. Do you have an idea on how I could fix these 2 issues?

Many thanks

 

[Misca]

That's weird: I get 397 unique Area Names with the same query code.

The whole reason why the Remove Errors step is there is because of all the "z" -values in the columns where the data type is changed to numbers. My code only removes errors from the "Conception rate per 1,000 women in age group" column because all the errors seemed to be on the same rows. But if you want to make sure all the rows with errors will be deleted you can change replace the original "Removed Errors" row in the code with

#"Removed Errors" = Table.RemoveRowsWithErrors(#"Pivoted Column"),

Note the comma at the end. The commas are needed in the code after every step but the very last one.