Create Separate Rows in Excel based on a Cell Value of Year Range

I have the following data in Excel 16.53

And I'd like to programmatically create separate rows based on the year range like this:

My goal is to keep all the other cell values in the row the same but change the year range into individual years with separate rows per year.

How can I accomplish this in a sheet with over 4k rows?

Solution 1:

Here's how to do this with Power Query.

• create a Table for your data with Ctrl+T. Give the table the name "MyTable" if you want the following code to work, or adjust accordingly.
• load the table into Power Query with "From Sheet" in the data ribbon.
• split the year column by the delimiter "-"
• create a custom column with a list of years from Year.1 to Year.2
• Expand the list by clicking the icon in the column header of the new column
• delete the columns you no longer need.

You don't need to write any code for this, just click on commands in the user interface, but when you look at the code, it looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Year", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Year.1", "Year.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year.1", Int64.Type}, {"Year.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "list", each {[Year.1]..[Year.2]}),
    #"Expanded list" = Table.ExpandListColumn(#"Added Custom", "list"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded list",{"Year.1", "Year.2"})
in
    #"Removed Columns"