Removing initial entries with value of 0 but not entries in between range

[jhwth]

I have a table that shows employee names, work date and the time in minutes they worked for that date:

Employee	Date	Minutes
Employee A	Aug 1	0
Employee A	Aug 2	0
Employee A	Aug 3	500
Employee A	Aug 4	500
Employee A	Aug 5	500
Employee B	Aug 1	100
Employee B	Aug 2	400
Employee B	Aug 3	0
Employee B	Aug 4	300
Employee B	Aug 5	300

I want to remove the entries they didnt work (minutes = 0) but only if it is before the first day they worked. So for example, I want to remove the Aug 1 and 2 entries for Employee A, but I want to keep the Aug 3 entry for Employee B, since the 0 value is between non-zero values.

[flying008]

Hi,@jhwth 

Please see below gif:

iif((IsEmpty([Row-1:Minutes]) or [Row-1:Minutes]=0) and [Minutes] =0 ,Null(),[Minutes])

******

If it can help you , please mark it as a solution and give a like for more share.

[jhwth]

flying008

Thank you! This formula was good at removing "leading" zeros, however it removed some of the zeros if they are in between non-zero values, even though they should not be removed.  For example:

Employee	Date	Time
A	Aug 1	0	this zero should be removed
A	Aug 2	0	this zero should be removed
A	Aug 3	600
A	Aug 4	600
B	Aug 1	300
B	Aug 2	300
B	Aug 3	0	this zero should be kept
B	Aug 4	0	this zero should be kept
B	Aug 5	400

[flying008]

Hi, @jhwth 

Employee	Date	Time
A	1-Aug	0	this zero should be removed
A	2-Aug	0	this zero should be removed
A	3-Aug	600
A	4-Aug	600
B	1-Aug	0	this zero removed or retained?
B	2-Aug	0	this zero removed or retained?
B	3-Aug	300
B	4-Aug	300
B	5-Aug	400

[jhwth]

flying008

Those 2 zeros you highlighted should be removed. The "leading" zeros per each employee should be removed.

[ShankerV]

Hi @jhwth 

Please find the expected output.

Input was:

Many thanks

Shanker V

[ShankerV]

Hi @jhwth 

Same workflow tested for your other input too.

Input used:

Many thanks

Shanker V

[flying008]

Hi, @jhwth 

There is a final way to get your want, even you have many employees or types minutes.

Hi, @ShankerV 

Thank you for your workflow, but the flow can't match below data:

Employee	Date	Minutes
Employee A	1-Aug	0
Employee A	2-Aug	0
Employee A	3-Aug	0
Employee A	4-Aug	500
Employee A	5-Aug	500
Employee B	1-Aug	0
Employee B	2-Aug	0
Employee B	3-Aug	0
Employee B	4-Aug	300
Employee B	5-Aug	300

********

[ShankerV]

Hi @flying008 

Thanks for the notification.

I worked the workflow based on the scenario shared by @jhwth  to accommodate the logic.

Have tweaked the formula to accommodate your request too.

Hope it helps!!!!

Employee	Date	Minutes
Employee A	1-Aug	0
Employee A	2-Aug	0
Employee A	3-Aug	0
Employee A	4-Aug	500
Employee A	5-Aug	500
Employee B	1-Aug	0
Employee B	2-Aug	0
Employee B	3-Aug	0
Employee B	4-Aug	300
Employee B	5-Aug	300

[flying008]

Hi, @ShankerV 

I guess the logic of @jhwth required like this:

Input					Output
Employee	Date	Minutes			Employee	Date	Minutes	Action
Employee A	1-Aug	0			Employee A	1-Aug	0	to be removed
Employee A	2-Aug	0			Employee A	2-Aug	0	to be removed
Employee A	3-Aug	0			Employee A	3-Aug	0	to be removed
Employee A	4-Aug	0			Employee A	4-Aug	0	to be removed
Employee A	5-Aug	500			Employee A	5-Aug	500
Employee B	1-Aug	100			Employee B	1-Aug	100
Employee B	2-Aug	0			Employee B	2-Aug	0
Employee B	3-Aug	0			Employee B	3-Aug	0
Employee B	4-Aug	300			Employee B	4-Aug	300
Employee B	5-Aug	300			Employee B	5-Aug	300

So there is need  a dynamic solution to figure out the result.

******

@jhwth  What are you really want ?