Replicate data from a table n number of times and increment one of the values for each iteration

[gavcol]

Hi,

I have a data table "Tbl_Allo" with a couple of thousand records but the below table is a good example subset of the data.

In	Out	Ref	Rate	Year	Period
AAA	A1	1A	0.0001	2026	01
BBB	#	1B	0.0467	2026	01
CCC	#	1C	0.1727	2026	01
DDD	D1	1D	0.1722	2026	01
AAA	A1	1A	0.5314053	2027	01
BBB	#	1B	0.0424033	2027	01
CCC	C1	1C	0.0157139	2027	01
DDD	D1	1D	0.0000541	2027	01

What I need to achieve, preferably with a formula but happy to use power query (but not VB), is a way to recreate the table a specific n number of times (n = a variable named value "Val_Repeat" ), however I need the Period value to increment at each replication up to a maximum of 12.

I have already created a horrifically long formula (takes up nearly the whole screen) with a mixture of LET, FILTER, VSTACK and HSTACK functions but I would like a more efficient way of producing the replicated data.
Would prefer to do it in a single formula. Note, because the data contains the # it caused a problem with earlier versions of the formula that used the sequence function.

Hoping someone can suggest a way to do this with a single formula and output to a single spill dataset.
Thank you

 

[gavcol]

Hi @djclements,
Sorry, I just noticed an issue with the formula.

The source data has a leading zero in the period column but the output has dropped the leading zero.
My apologies, I have probably given you a bum steer because my example above also didn't include the leading zero !!
I haven't been able to modify it to retain the leading zero.
Could you pls assist ?

 

[djclements]

The source data has a leading zero in the period column, but the output has dropped the leading zero...

Try using the TEXT function on the period array, e.g. TEXT(period,"00")

=LET(
   Val_Repeat, 12,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat,,0),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))),
   SORT(HSTACK(DROP(a,,-1),TEXT(TAKE(a,,-1)+TOCOL(IF(x,y)),"00")),5)
)

 

[gavcol]

Try using the TEXT function on the period array, e.g. TEXT(period,"00")

=LET(
   Val_Repeat, 12,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat,,0),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))),
   SORT(HSTACK(DROP(a,,-1),TEXT(TAKE(a,,-1)+TOCOL(IF(x,y)),"00")),5)
)

Thanks. Not sure what happened but when I opened the file again, to make the change you provided above, it had updated with the leading zeros.
Calculation has always been automatic so I'm unsure why it didn't update after I introduced the leading zeros in the source data, unless it took longer to refresh the resulting near half a million records.

 

[djclements]

Thanks. Not sure what happened but when I opened the file again, to make the change you provided above, it had updated with the leading zeros.
Calculation has always been automatic so I'm unsure why it didn't update after I introduced the leading zeros in the source data, unless it took longer to refresh the resulting near half a million records.

Not sure what you mean either. By the way, if the Period column in your source table (Tbl_Allo) is just filled with "01" for the first period, you can improve performance by dropping this column and utilizing the y sequence instead:

=LET(
   Val_Repeat, 12,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, TEXT(SEQUENCE(Val_Repeat),"00"),
   SORT(HSTACK(CHOOSEROWS(DROP(Tbl_Allo,,-1),TOCOL(IFNA(x,y))),TOCOL(IF(x,y))),5)
)

That way, you won't be lifting/broadcasting 2 additional calculations over half a million records.