Multi-Row Formula to duplicate row data across all Fields in a data set

[SharkeyNZ]

Hi All,

I have a data set that I need to clean up before I can use it in a flow.  The data is a cut and paste of a pivot table display of data where the field values are not repeated.

For example (Note that my actual data has about 50 fields):

Record	Name	Contract	Status	Start Date	End date	Value
1	Bob	X123	Draft Amendment	1/1/2023	31/01/2023	1
2	[Null]	[Null]	[Null]	1/1/2021	31/01/2021	100
3	[Null]	[Null]	Published	1/2/2023	31/12/2023	1
4	[Null]	[Null]	[Null]	1/2/2021	31/12/2022	100
5	[Null]	X456	Draft	1/1/2022	1/2/2023	10
6	[Null]	[Null]	Published	2/2/2023	31/12/2025	[Null]
7	Fred	X234	Published	1/1/2022	1/1/2026	500

The top row are the Field names, except for "Record"

I need to update each field in a row, so that the field value, if null, is equal to the previous Row value.  For example:

• Record 2, the Field [Name] value is [Null], so Record 2 [Name] should update to "Bob" from Row 1, field [Name].
• Record 2, the Field [Contract] value is [Null], so Record 2 [Contract] should update to "X123" from Row 1, field [Contract].
• This should continue across all the fields in Row 2.
• Record 3, the Field [Name] value is [Null], so Record 3 [Name] should update to "Bob" from Row 2, field [Name]. - assuming it was updated in the previous step.
• and repeat for all fields and all rows.

So the resulting table would look like this:

Record	Name	Contract	Status	Start Date	End date	Value
1	Bob	X123	Draft Amendment	1/1/2023	31/01/2023	1
2	Bob	X123	Draft Amendment	1/1/2021	31/01/2021	100
3	Bob	X123	Published	1/2/2023	31/12/2023	1
4	Bob	X123	Published	1/2/2021	31/12/2022	100
5	Bob	X456	Draft	1/1/2022	1/2/2023	10
6	Bob	X456	Published	2/2/2023	31/12/2025	10
7	Fred	X234	Published	1/1/2022	1/1/2026	500

I know I can do this using the the Multi-Row Formula tool, but I only know how to do this on an individual field basis, so I would have to replicate the tool 50 times or for as many Fields as I have.

I assume that a Batch Macro might work, but I'm stuck on how to start that.

Cheers

Craig

[PhilipMannering · Accepted solution]

The usual solution to this kind of problem is pivoting your data,

[PhilipMannering]

You can also do this in the Python Tool with the following code,

from ayx import Alteryx
df = Alteryx.read('#1')
df = df.ffill()
Alteryx.write(df, 1)

[SharkeyNZ]

Thanks, that works perfectly and I see the logic I was missing in other examples.