Start Date Range

[geirselvag]

Hi @v-jiascu-msft 
Notice your tips regarding other Power Query - date challenges. My current challange I need tips of how am able to evaluate, e.g. get the correct row value (factor) based on transaction date.

Extract Factor from Table having fixed values:
 Start date, End date, Factor
01.01.2016, missing, 3
01.01.2020, missing, 4
01.01.2022, missing, 5

The condition is our missing End date is based on next change of start date, e.g. transaction date 25.05.2019 should give factor 3 thus transaction date between Start date and End date (our missing date 31.12.2019). Would like to create a function that retreive correct factor. E.g. find factor based on transaction date of 25.05.2019 = 4.

Appreciate tips or guidance of Power Query - function.
Thanks, Geir

[v-angzheng-msft · Accepted solution]

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.