How to cleans data from leading 0 values in time series

Hi all,
I have a data series where I have several products (A...C) and data over multiple periods. The data shows the uptake of values at a certain moment in time (period) and has leading 0 values. After the start, values may drop to 0 (or even below).
I am looking for an EasyMorph process to remove or skip the leading 0 until the start of the respective product's uptake but not clear for all 0 values. The intention is to create a "relative" period dimension where the period #1 is the first period of the uptake.
For selectively removing the leading 0s 'Filter' or 'CleanUp' does not work.

Attached an sample Excel table where column D (Relative Period) would be the column I am looking to achieve.

Thanks for any leads.
TestData.xlsx (17.7 KB)

Hello Claude,

I would take the following approach:

  • assign a sequential row number to each input row (i.e. each row in the input dataset), split by each Product. You can use the 'Enumerate rows' action.

  • in an intermediate step (i.e. a separate derived table), remove the rows where "Value = 0". Then compute the minimal input row number for each Product. This gives, for each Product, the first row where the values are not equal to zero.

  • merge this intermediate table back into the input table. Based on both row numbers (namely the input row number, and the first non-zero row number), one can compute the relative Period column as follows:

    if [RowNo] < [FirstNonZeroRowNo]
    then empty()
    else [RowNo] - [FirstNonZeroRowNo] + 1

The .morph file attached uses this approach.

Kind regards,


claja_relative_period.morph (6.1 KB)


Great solution - solves the question. Thanks for the rapid support.
Kind regards,