I have a table which contains schedules for my employees, broken out into their start and end times. Like so:
<tbody>
</tbody>
And I have a list of dates which I join this table to, which ends up looking like this:
<tbody>
</tbody>
After this, I want to create two new fields: Expected_Punch_In and Expected_Punch_Out, which are based on the Start and End times for a given day, and then I want to Remove the individual Monday/Tuesday/Wednesday/etc._Start/End columns. I'm currently doing this the long, ugly, kludgy way of having Power Query iterate through the long chain of if statements like so:
if [DoW] = "Monday" then [Monday_Start]
else if [DoW] = "Tuesday" then [Tuesday_Start]
etc.
but what I'd like to figure out how to do is to to make that process dynamic so that Power Query figures out which column to pull from based on the value in the DoW field..
Or, in other words, how do I replicate the Excel INDIRECT function in Power Query?
| Employee | Monday_Start | Monday_End | Tuesday_Start | Tuesday_End |
| Bob | 09:30:00 | 18:00:00 | 09:30:00 | 16:30:00 |
| Alice | 08:00:00 | 16:30:00 | 08:00:00 | 16:30:00 |
<tbody>
</tbody>
And I have a list of dates which I join this table to, which ends up looking like this:
| Date | DoW | Employee | Monday_Start | Monday_End | Tuesday_Start | Tuesday_End |
| 01/01/2017 | Monday | Bob | 09:30:00 | 18:00:00 | 09:30:00 | 18:00:00 |
| 01/01/2017 | Monday | Alice | 08:00:00 | 16:30:00 | 08:00:00 | 16:30:00 |
| 01/02/2017 | Tuesday | Bob | 09:30:00 | 18:00:00 | 09:30:00 | 18:00:00 |
| 01/02/2017 | Tuesday | Alice | 08:00:00 | 16:30:00 | 08:00:00 | 16:30:00 |
<tbody>
</tbody>
After this, I want to create two new fields: Expected_Punch_In and Expected_Punch_Out, which are based on the Start and End times for a given day, and then I want to Remove the individual Monday/Tuesday/Wednesday/etc._Start/End columns. I'm currently doing this the long, ugly, kludgy way of having Power Query iterate through the long chain of if statements like so:
if [DoW] = "Monday" then [Monday_Start]
else if [DoW] = "Tuesday" then [Tuesday_Start]
etc.
but what I'd like to figure out how to do is to to make that process dynamic so that Power Query figures out which column to pull from based on the value in the DoW field..
Or, in other words, how do I replicate the Excel INDIRECT function in Power Query?
Last edited:
