Keep only the first non-null value for each column

[oce]

Hello, I have to apply the following transformation on my dataset which consists of many columns (~30):

I have tried applying the following multi-row formula:

IF IsNull([Row-1:A]) and !IsNull([A]) THEN [A] ELSE NULL() ENDIF

but since it only applies to a single column it doesn't seem feasible to repeat for each column. Is there a way to do this in a more efficient way? 

[Maskell_Rascal]

Hi @oce 

Here is how you can do it. 

Input:

Workflow:

Let me know if that works for you. 

Cheers!

Phil

[mceleavey]

Hi @oce ,

a simple bit of pivoting and sampling will do the trick.

I pivoted the data to filter out all nulls in the value columns, then sampled the first of each grouped by name and pivoted back.

Simple.

Hope this helps,

M.

EDIT: Sorry, I just did this to retrieve ONLY the first ones. If you want to keep it uniform, simply use the recordID field as the group by in the pivot and remove the filter .

[atcodedog05 · Accepted solution]

Hi @oce 

My take on it using the multi-row formula tool.

Workflow:

Hope this helps : )