Power Query M Code to look up value at the intersection of specific record & field

[Anonymous]

I come from an Excel user background & are trying to build my skills in Power BI & specifically Power Query M Code. My business have asked me to write a simple Power BI model to report graphically on KPIs.  To achieve this I am trying to build a calendar table that will dynamically know when todays date is so it can calculate figures for last week, last period etc.  I have searched the internet & found a number of people who have achieved this for standard calendar months, but not a system that can cope with the irregular pattern of 5+4+4 week quarters adjusted every few years to bring the year end back to the Saturday closest to 30 June, that is used by my company.

Therefore I have written the basic calendar table in Excel to link dates to financial years periods & weeks, & added uniquie index numbers for each.  I have then imported it into Power Query & written some M code that knows todays date & can show if a specified date is past or future, & whether it is today.  Up to that point it works fine.  I then need some Mcode that will find the record where IsToday=Today & return the value in the Week Index colomn.  This would be = the Current Week Index & deducting it from each Week Index for each record would give a week offset enabling the report to show this week, last week, 52 weeks ago etc.  This would be a simple LOOK UP exercise in Excel but I am struggling to do it in M code.

The code I have written so far is

This inserts the Current Week Index field but shows an error

The error expands to

Have you any suggestions as to the M code I need to complete this look up?

[lbendlin · Accepted solution]

Personal opinion/word of advice:  Don't do it (in Power BI). Use an external dates/calendar table that you maintain in an Excel file on a sharepoint, or in a SQL Server table.  That will not just save you lots of works, but also (hopefully) give you back some sanity. Especially with your off-standard fiscal calendar.