How to replicated an IF formulan in PowerQuery

Hi All,

I have a IF formula in excel which is working fine for accumulating a sickness day calculation for employees. But some reasons I will move the file to power query and therefore I need to replicate the formulas in Powerquery. But my power query knowledge is limited for that. In this point, may I kindly ask your help on how to achieve same result in power query ?

So, here is my attached file. I have a table with employees sickness start dates which is "Date" column. Based on company rule, if the sickness is taken more than 3days in 7 days time period, first 3 days is % 50paid and rest is unpaid.
That's our holy rule In "P" column I am checking the 7day rules and In "Q" column, i am indexing the continuous dates. if its not continuous/in 7 days; i am starting from 1. At the end, I will mark everything <=3 as paid, rest unpaid which is the easy part.

Any idea how to do this indexing (column Q) in Power Query ?

PS: "employee logon" column is for keeping the calculation for that spesific employee. Thats why, there is always if E3=E2 formula.

Let me know if you need anything. Any tips or start point for me in power query would be also much appreciated.

Thanks a million
Orhan

Capture.JPG

When you say "move this to Power Query", what exactly do you mean? Power Query is to help you reorganise and pivot data: your working data doesn't reside there.

The way it works is this:

1. Get your SOURCE data into Power Query.
2. Reorganise/pivot your data into the format you need.
3. Add calculated columns (such as the one you are describing) to the OUTPUT table (i.e. the one that you created in PQ).

You may find this thread from yesterday of interest in this regard: https://www.excelforum.com/excel-for...ry-output.html

Thank you for the feedback. But I am trying to achieve wider outcome then this. Like with this result I described above, I will merge with other queries and do a specific report. Also I am gathering the above source file with different reports as merging them with power query. Unfortunately, I cant pull a step from the middle and do the accumulating via normal excel and continue with merging in power query. Instead, I am trying achieve everything in power query with a button of refresh.

Thanks again
Orhan

OK - that clarifies it - thanks.

Thank you very much rorya ! It is working partly with a small gap.

I inserted a column to your excel (please see the attachment), to show the differences with current excel formula. The one which is starting from 5th row with 21st June, 22nd June, 23rd June is reflected with 1 - 2 - 3 consecutive days which is correct. the issue is following 8th row has 28th June which has less then one week difference. So, in those cases indexing should continue with 4 - 5 - 6 etc...

Sorry maybe i wasnt clear enough with below rule:

" if the sickness is taken more than 3days in 7 days time period, first 3 days is % 50paid and rest is unpaid."

My aim is, log the days which is more than 3 in 7days period, and then point them as unpaid. with a simple if formula > if consecutive days > 3, unpaid.

Hope its clear now. Any suggestion for 7 day rule ?

Thanks a million again for your support and your time on this.

Orhan