Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
winniekoh69
New Member

Replicating Index/Match Function in Power Query Excel

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?

1 REPLY 1
v-sihou-msft
Employee
Employee

@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,

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.