Split column to rows

[hnorgaar]

Hi, i have a sheet with 2 years, column 1=2010 and column 2=2015
How can I "split" so I get 6 rows like 2010, 2011, 2012, 2013, 2014, 2015? Thx for any input

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Years", each {[Start]..[End]}),
    #"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Years",{"Years"})
in
    #"Removed Other Columns"

Book1
	A	B	C	D
1	Start	End		Years
2	2010	2015		2010
3				2011
4				2012
5				2013
6				2014
7				2015
Sheet1

 

[etaf]

how about
=SEQUENCE(B1-A1,1,A1)
I maybe missing what you need
where B1 is column with 2015 and A1 is column with A1

Book6
	A	B	C	D
1	2010	2015		2010
2				2011
3				2012
4				2013
5				2014
Sheet2

Cell Formulas
Range	Formula
D1:D5	D1	=SEQUENCE(B1-A1,1,A1)
Dynamic array formulas.

 

[alansidman]

@etaf
I think that you need to amend your formula as =SEQUENCE(B1-A1+1,1,A1) to make sure you get 2015

 

[hnorgaar]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Years", each {[Start]..[End]}),
    #"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Years",{"Years"})
in
    #"Removed Other Columns"

Book1
	A	B	C	D
1	Start	End		Years
2	2010	2015		2010
3				2011
4				2012
5				2013
6				2014
7				2015
Sheet1

Thx, fixed my problem