+ Reply to Thread
Results 1 to 11 of 11

How to replicated an IF formulan in PowerQuery

  1. #1
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    118

    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
    Attached Files Attached Files
    Last edited by orhanceliloglu; 07-20-2018 at 05:44 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,672

    Re: How to replicated an IF formulan in PowerQuery

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    118

    Re: How to replicated an IF formulan in PowerQuery

    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
    Last edited by AliGW; 07-20-2018 at 06:03 AM. Reason: Quotation removed - unnecessary.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,672

    Re: How to replicated an IF formulan in PowerQuery

    OK - that clarifies it - thanks.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,505

    Re: How to replicated an IF formulan in PowerQuery

    There are probably simpler ways, but here's one.

    Load the table to PQ, making sure it's sorted on employee and date) then duplicate/reference it. Add an index column starting at 1 to one copy (call it say Table1PrevIndex). Then to the other add an index starting at 0. You can then merge the two on the index column, so that you can get the previous date and employee for each row, and calculate the difference in days to the prior row (if the employee changes, return 0). You can then add another calculated column that says that if the days difference is not 1, return the Apply date, otherwise return null. Then fill down this column, which will give the start date of any absence period in each row. Then just subtract that from the apply date to get the number of consecutive days off.

    Sample attached.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    118

    Re: How to replicated an IF formulan in PowerQuery

    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
    Attached Files Attached Files
    Last edited by AliGW; 07-25-2018 at 06:11 AM. Reason: Quotation unnecessary - removed.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,505

    Re: How to replicated an IF formulan in PowerQuery

    That is actually more complex than your existing formula properly caters for. For example, if your dates were 21/6/2018, then 27/6/2018 and 3/7/2018, your current formula would treat that as 3 consecutive days (since each gap is only 6 days) even though it's not really three days within a 7 day period.

  8. #8
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    118

    Re: How to replicated an IF formulan in PowerQuery

    Quote Originally Posted by rorya View Post
    That is actually more complex than your existing formula properly caters for. For example, if your dates were 21/6/2018, then 27/6/2018 and 3/7/2018, your current formula would treat that as 3 consecutive days (since each gap is only 6 days) even though it's not really three days within a 7 day period.
    Sorry if i wasnt clear in the first place but yes you are right. If the case is as below, then consecutive days should be 1,2,3,4,5,6. That's my aim. Do you think its too complex for powerquery ?

    Thanks a lot
    Orhan


    Apply Date Consecutive_days
    6/21/2018 1
    6/27/2018 2
    7/3/2018 3
    7/9/2018 4
    7/15/2018 5
    7/21/2018 6

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,505

    Re: How to replicated an IF formulan in PowerQuery

    So even though that would not involve 3 days within any 7 day period, it counts? That doesn't seem to tie up with the original description of your rule.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,505

    Re: How to replicated an IF formulan in PowerQuery

    Assuming that is actually what you want, try this version.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    118

    Re: How to replicated an IF formulan in PowerQuery

    Quote Originally Posted by rorya View Post
    Assuming that is actually what you want, try this version.
    Exactly this is what I want ! I am so grateful. thanks a million !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mirror Cells for edits to be replicated both ways
    By hemal89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-27-2019, 06:14 PM
  2. Formulas not working on replicated sheet
    By chilli76 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2016, 04:52 AM
  3. Need Columns to Feed to Replicated Questionnares
    By ablits1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2014, 11:46 AM
  4. Replicated adding/deleting rows on seperate sheets
    By hlep in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-23-2012, 05:41 AM
  5. Replies: 1
    Last Post: 05-21-2012, 03:00 AM
  6. Vlookup to find missing or replicated data`
    By excelhlp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2008, 05:24 PM
  7. Unwanted replicated split screen
    By BoyLeroy in forum Excel General
    Replies: 1
    Last Post: 09-28-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1