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

[Syndicate_Admin]

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):

(Example for copy/paste)

Qdates (dd/mm/yyyy)
16/05/2024
15/05/2024
14/05/2024
10/05/2024
05/05/2024
02/05/2024
01/05/2024
30/04/2024
29/04/2024
28/04/2024
27/04/2024
26/04/2024
02/04/2024
01/04/2024
31/03/2024
30/03/2024
23/03/2024
13/03/2024
02/03/2024
01/03/2024
29/02/2024
28/02/2024
27/02/2024
26/02/2024
25/02/2024

The second (a few dozen lines) with dates indicating the end of periods in descending order as well:

PDates (dd/mm/yyyy)
30/04/2024
31/03/2024
29/02/2024

These fields are in “date” format.

My track (good or bad) :

Until then I interpret the second table as a list where the first item (here 30/04/2024) has an index {0} and the third (29/02/2024) has an index {2}.

I would like to be able to add a column to the first table which would return the index of the end of the previous period (from my list therefore), such that, chronologically, for example, dates from and after 30/04/2024 retrieve the index {0}. Later, the index will be used to do a “VLookUp” to retrieve other values… but after that I should be able to manage it.

For the moment my logic is to truncate the "PDates" list by doing a "ListRemoveItems" of dates greater than [QDates], to then keep only the "List.Max" item from my list (thus the end of the previous period and especially its index).

But I have 2 problems:

- A value type problem which does not allow me to truncate my list with the date value from [QDates] (although it works if I use a fixed date by hand in the code!)

- Then I risk losing the initial index by truncating my list (but I was planning to do a "List.Contains" on the initial list to find it).

Et voilà !

Otherwise, more simply, the expected result is this:

Qdates IndexPDates
16/05/2024 0
15/05/2024 0
14/05/2024 0
10/05/2024 0
05/05/2024 0
02/05/2024 0
01/05/2024 0
30/04/2024 0
29/04/2024 1
28/04/2024 1
27/04/2024 1
26/04/2024 1
02/04/2024 1
01/04/2024 1
31/03/2024 1
30/03/2024 2
23/03/2024 2
13/03/2024 2
02/03/2024 2
01/03/2024 2
29/02/2024 2
28/02/2024 Null
27/02/2024 Null
26/02/2024 Null

Thanking you in advance for your consideration and your help.

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

Thank you @HotChilli ,

You're solution is good. Thank you too to @v-jingzhan-msft for your percision.

Excuse my delayed comment, I had to find time... and I wanted to try to add value to the topic.

I found that it was a good idea to "buffer" the list in which I wanted to get the index to improve speed of the query.

So my final query look like this :

let
BufferedPDatesList = List.Buffer(Table.Column(#"PDatesTable", "date")),
Source = Table.AddColumn(
#"QDates",
"IndexInPDatesList",
each let x = [date] in List.PositionOf(BufferedPDatesList, List.Max(List.Select(BufferedPDatesList, each _ <= x ))))
in
Source