Replicating Index/Match Function in Power Query Excel

[winniekoh69]

I have two data tables.

1. Date - the important fields here are "Week Start", "Week Number"

2. Refer Date - the important fields here are "Week Start Date", "Refer Week Number"

I match the Refer Date records to the Date based on it being the same Week Start Date falling between the Week Number.

Eg:
Date Table
Week Start   Week Number
06/04/2015        1
13/4/2015          2
20/4/2015          3

Refer Date Table
Week Start Date    Refer Week Number
06/04/2015                        1
07/04/2015                        2
13/4/2015                          2
14/4/2015                          3

In the Excel Function, =(MAX(INDEX('Date'!$A$2:$B$4,MATCH('Refer Date'!A2,'Date'!$A$2:$A$4,-1)),1))

Any idea how to replicate in Power Query?

[v-sihou-msft]

@winniekoh69

I'm still not quite clear about your requirement based on the expression. If you want to get the week number based on match Date and Refer Date, you can use LOOKUPVALUE() function.

=LOOKUPVALUE('Refer'[Week Number],'Date'[Week Start],'Refer'[Week Start])

Regards,