Shift Nulls to the bottom

Question

Hi,

I receive files constantly, and each time the number of columns and rows varies. There would be nulls in different places. I would like to know how to push all nulls from each column to the last rows of that column. Below you can see an example of how my data is structured, and how I want them to be. Also, attached a workflow that can do it. But, it is not what I am looking for since it is not dynamic which I need because the number of columns would vary each time.

Original Data sampleNeeded Data sample

Sample Nulls shifting.yxmd

jdunkerley79 · Accepted Answer

I think something like:

First add a record ID (for ordering)

Then transpose the data and filter for nulls

Create a Rank for each column using a multi-row formula grouped by field

Next cross tab grouped by Rank

The final join is just to reproduce the extra null rows and can be skipped if not needed.

Sample attached