Merge with Date Lookup

Power Query
1

I have a Transaction Table that specifies the Branch of the person who approved the Transaction.

I have another Table (Table2) with a date and Name. I want the Output of the PQ to insert a column into Table2 showing for each row the Branch the person was at on the date on the row according to Transaction Table.

eg the Transaction table says that on 21/3/20 Sally was at East so, in Table2 the row for Sally dated 20/6/20 will show East.

The Orange Column is the Output I should get.

I am pretty sure it is a merge I am wanting – but how do you do this with conditional lookup logic?

Thank You

Effective Date.xlsx (11.9 KB)

2

@AllisterB

Is this what you are expecting ?

image
image838×170 7.96 KB

image

Merge with Date Lookup.pbix (21.0 KB)

3

Hi

I am reading your solution on my phone so may not be seeing things clearly… However I was expecting the output on the orange column of my workbook

4

@AllisterB

I missed your excel file… Will provide the solution soon

5

Hi Rajesh

Have you been able to look at this yet?

Allister

6

@Rajesh

Have you been able to re look at this yet?

Allister

7

@AllisterB

Are you looking for only PQ solution ? I think its little difficult in PQ

Here is the solution using DAX.

image
image1313×583 32 KB

Merge with Date Lookup.pbix (21.3 KB)

1 Like
8

@AllisterB

Here is the solution using PQ. Provided solutions using both PQ and DAX.

image
image1536×632 29.4 KB

Merge with Date Lookup.pbix (22.3 KB)

2 Likes
9

Thanks Rajesh

Yes I was looking for a PQ solution.

Kind Regards

Allister

10

@Rajesh

Hi Rajesh

Yes I was looking for a PQ solution.

Kind Regards

Allister