Dynamic Column Reference in Power Query

Guttural

New Member
Joined
Jan 21, 2015
Messages
9
I have a table which contains schedules for my employees, broken out into their start and end times. Like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Monday_Start[/TD]
[TD]Monday_End[/TD]
[TD]Tuesday_Start[/TD]
[TD]Tuesday_End[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]09:30:00[/TD]
[TD]18:00:00[/TD]
[TD]09:30:00[/TD]
[TD]16:30:00 [/TD]
[/TR]
[TR]
[TD]Alice[/TD]
[TD]08:00:00[/TD]
[TD]16:30:00[/TD]
[TD]08:00:00 [/TD]
[TD]16:30:00 [/TD]
[/TR]
</tbody>[/TABLE]

And I have a list of dates which I join this table to, which ends up looking like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]DoW[/TD]
[TD]Employee[/TD]
[TD]Monday_Start[/TD]
[TD]Monday_End[/TD]
[TD]Tuesday_Start[/TD]
[TD]Tuesday_End[/TD]
[/TR]
[TR]
[TD]01/01/2017[/TD]
[TD]Monday[/TD]
[TD]Bob[/TD]
[TD]09:30:00[/TD]
[TD]18:00:00[/TD]
[TD]09:30:00[/TD]
[TD]18:00:00[/TD]
[/TR]
[TR]
[TD]01/01/2017[/TD]
[TD]Monday[/TD]
[TD]Alice[/TD]
[TD]08:00:00[/TD]
[TD]16:30:00[/TD]
[TD]08:00:00 [/TD]
[TD]16:30:00 [/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]Tuesday[/TD]
[TD]Bob[/TD]
[TD]09:30:00[/TD]
[TD]18:00:00 [/TD]
[TD]09:30:00 [/TD]
[TD]18:00:00 [/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]Tuesday[/TD]
[TD]Alice[/TD]
[TD]08:00:00[/TD]
[TD]16:30:00 [/TD]
[TD]08:00:00 [/TD]
[TD]16:30:00 [/TD]
[/TR]
</tbody>[/TABLE]


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:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top