Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |