Forum Discussion

Titouli's avatar
Titouli
New Member
2 years ago
Solved

Adding a column returning an index from a list of dates (Index for a "VLookUp")

Hi everyone, Here is the problem that I want to solve in POWER QUERY (in M Language). I have 2 tables: The first (several hundred of rows) presents a date field [QDates] (descending order):   (E...
Need Help
  • HotChilli's avatar
    HotChilli
    2 years ago

    In Power Query, add a custom column in the first table :

    let x = [Qdates] in List.Max(List.Select(Table2[PDates], each _ < x))

    This will add the matching date that you require.

    I'll leave it to you to add the 0 or 1 or 2 which you can get from the date.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Titouli 

 

HotChilli 's solution is great. To meet the expected result, modifying it a little "...... each _ <= x " at the end seems better. 

 

In addition, as your purpose is probably to retrieve other values from Table2 based on the matching dates, you can use the Merge queries feature. With the matching date column, you don't have to add and use an Index column. 

 

Best Regards,
Jing