Start Date Range

Hi, geirselvag 

Create a blank query, open Advanced Editor and replace the text there with the code below.  In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your "Date" column as the input.

 

Sample:

 

Code:

(lookup_value as any, table_array as table, col_index_number as number, optional approximate_match as logical ) as any =>

let

/*Provide optional match if user didn't */

matchtype =

if approximate_match = null

then true

else approximate_match,

/*Get name of return column */

Cols = Table.ColumnNames(table_array),

ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

ColName_match = Record.Field(ColTable{0},"Column1"),

ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),

/*Find closest match */

SortData = Table.Sort(table_array,{{ColName_match, Order.Descending}}),

RenameLookupCol = Table.RenameColumns(SortData,{{ColName_match, "Lookup"}}),

RemoveExcess = Table.SelectRows(RenameLookupCol, each [Lookup] <= lookup_value),

ClosestMatch=

 

if Table.IsEmpty(RemoveExcess)=true

then "#N/A"

else Record.Field(RemoveExcess{0},"Lookup"),

/*What should be returned in case of approximate match? */

ClosestReturn=

if Table.IsEmpty(RemoveExcess)=true

then "#N/A"

else Record.Field(RemoveExcess{0},ColName_return),

/*Modify result if we need an exact match */

Return =

if matchtype=true

then ClosestReturn

else

if lookup_value = ClosestMatch

then ClosestReturn

else "#N/A"

in Return

 

refer:

Working with ranges (Numbers/Dates) in Power Query/Power BI - Part 1

 

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.