Fill in rows as per first row

[Luqman]

Hi

I need to quickly fill in the empty collumn

UTR (level 1& 2)	Transaction Type (Subscription / Redemption)	Total Value	NAV Date	Sub Fund	T0	T1	T2	T3	T4	T5
Level 1	Redemption	-10696254.21	04-Jul-24	UTR -TFO	#	#	#	#	#	#
	Redemption	0		PruEmpire
	Redemption	-401305.5333		UTR -TFO
	Redemption	-2376941.413		UTR -TFO
	Subscription	37291.57333		UTR -TFO
	Redemption	-170306.7867		UTR -TFO
	Subscription	9589.546667		UTR -TFO
	Subscription	2903.706667		UTR -TFO
	Subscription	66079.04		UTR -TFO
	Subscription	44057.42667		UTR -TFO
	Subscription	46087.42667		UTR -TFO
	Subscription	28296.4		UTR -TFO

into this

UTR (level 1& 2)	Transaction Type (Subscription / Redemption)	Total Value	NAV Date	Sub Fund	T0	T1	T2	T3	T4	T5
Level 1	Redemption	-10696254.21	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	0	04-Jul-24	PruEmpire	#	#	#	#	#	#
Level 1	Redemption	-401305.5333	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	-2376941.413	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	37291.57333	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	-170306.7867	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	9589.546667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	2903.706667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	66079.04	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	44057.42667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	46087.42667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	28296.4	04-Jul-24	UTR -TFO	#	#	#	#	#	#

Using multi-row formula works... but i have to set up for each column, is there a faster way?

if isnull([UTR (level 1& 2)])
then
[Row-1:UTR (level 1& 2)]

else [UTR (level 1& 2)] endif

[Qiu · Accepted solution]

@Luqman 
Combination of Transpose and CrossTab is good for multil column data processing.
I added some small tricks to restore the original field names and field order.

[Luqman]

Hi @Qiu 


Thankss, i got how you use transpose and tile to fill in the blanks


but can i get your help on the final bit.. ie to re-arrange back the columns as per the original data

the tool you use, 'dynamic rename' only renamed the Column name, without changing the data

[Qiu · Accepted solution]

@Luqman 
The Tile is used to retrieve the Column Order in the original data and then as you understand, the Dynamic Rename is used to change back to the oringal naming without changing the data itself.

[Luqman]

@Qiu 

Got it now.

I just needed to add a tool to remove 'record id' as this wasnt in the original data

thankss

[Qiu]

@Luqman 
Good and you are welcome