Proper Data set from Date Range

[MrMishutka]

To take a table such as this

And turn it into a table like this

Is pretty easy in Power Query

let
  Source = Excel.CurrentWorkbook(){[Name="BaseData"]}[Content],
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Start", type date}, {"End", type date}, {"Type", type text}, {"Description", type text}}),
  #"Added number of days" = Table.AddColumn(#"Changed column type", "Days", each Duration.Days([End] - [Start]), Int64.Type),
  #"Add list" = Table.AddColumn(#"Added number of days", "List", each {0..[Days]}),
  #"Expanded list" = Table.ExpandListColumn(#"Add list", "List"),
  #"Added dates" = Table.AddColumn(#"Expanded list", "Date", each Date.AddDays([Start],[List]), type date),
  #"Removed other columns" = Table.SelectColumns(#"Added dates", {"Date", "Type", "Description"})
in
  #"Removed other columns"

But does anyone know how to do this with dynamic array formulae? I have been trying with a combination of BYROW, SEQUENCE and TOCOL, but keep geeting a #VALUE error because it says I have nested arrays.

I got close with a list of dates using in cell I2

=SEQUENCE(1,OFFSET(BaseData,H2-1,1,1,1)-OFFSET(BaseData,H2-1,0,1,1)+1,OFFSET(BaseData,H2-1,0,1,1),1)

where H2 contained

=SEQUENCE(ROWS(BaseData))

I could copy this down against each row in the table and then use

=TOCOL(I2#:I4#,1)

but the problem with this it is not dynamic and has to be updated everytime a new record is added to the table, in which case I might as well use the simple Power Query method above.

I tried to change H2 to H2# throughout the formula and I get a #VALUE! error. Equally if I change I2 to

=BYROW(H2#,LAMBDA(r,SEQUENCE(1,OFFSET(BaseData,r-1,1,1,1)-OFFSET(BaseData,r-1,0,1,1)+1,OFFSET(BaseData,r-1,0,1,1),1)))

I get a #CALC! (Nested Arrays) error.

Does anyone have any suggestions how I can get a proper data set using dynamic array formulae?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	01/01/2024	03/01/2024	Holiday	abc		01/01/2024	Holiday	abc
3	05/01/2024	08/01/2024	Work	mno		02/01/2024	Holiday	abc
4	02/02/2024	07/02/2024	Work	xyz		03/01/2024	Holiday	abc
5						05/01/2024	Work	mno
6						06/01/2024	Work	mno
7						07/01/2024	Work	mno
8						08/01/2024	Work	mno
9						02/02/2024	Work	xyz
10						03/02/2024	Work	xyz
11						04/02/2024	Work	xyz
12						05/02/2024	Work	xyz
13						06/02/2024	Work	xyz
14						07/02/2024	Work	xyz
15
Sheet5

Cell Formulas
Range	Formula
F2:H14	F2	=LET(d,B2:B4-A2:A4+1,m,MAX(d),s,SEQUENCE(,m),HSTACK(TOCOL(IF(s<=d,A2:A4+SEQUENCE(,m,0),1/0),2),TOCOL(IF(s<=d,C2:C4,1/0),2),TOCOL(IF(s<=d,D2:D4,1/0),2)))
Dynamic array formulas.

 

[MrMishutka]

2	=LET(d,B2:B4-A2:A4+1,m,MAX(d),s,SEQUENCE(,m),HSTACK(TOCOL(IF(s<=d,A2:A4+SEQUENCE(,m,0),1/0),2),TOCOL(IF(s<=d,C2:C4,1/0),2),TOCOL(IF(s<=d,D2:D4,1/0),2)))

Thats really cool, thanks. Now I will just have to work out why it is working

 

[Fluff]

You're welcome & thanks for the feedback.