Keep only the first non-null value for each column

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?

Workflow

Input

Transformation

Dynamic Processing

Expression

Accepted answers

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

Workflow:

Hope this helps : )

All comments

Here is how you can do it.

Input:

Workflow:

Let me know if that works for you.

Cheers!

Phil

Keep First Non-Null Value Each Column.yxmd

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 .

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

Workflow:

Hope this helps : )

Quick Links

Tags

This months top contributors

Hollingsworth 335