Return a list based on multiple conditions in Power Query

[ak123]

I have some forecast data which is currently structured like so (i made the below in excel just to give you an idea) -

forecast timestamp	settlement date	Period 1	Period 2	…	Period n
01/01/2000 09:30	05/01/2000	0.899914	0.451563	0.608477	0.009698
	06/01/2000	0.891523	0.82422	0.030027	0.0654
	07/01/2000	0.663729	0.297937	0.874033	0.743295
	08/01/2000	0.500766	0.565275	0.142954	0.652788
	09/01/2000	0.194198	0.017105	0.936981	0.884122
	10/01/2000	0.50848	0.488765	0.689477	0.405967
01/01/2000 13:30	05/01/2000	0.822775	0.338863	0.556413	0.194292
	06/01/2000	0.143446	0.136207	0.742118	0.850311
	07/01/2000	0.623409	0.193469	0.142194	0.321664
	08/01/2000	0.810111	0.017459	0.896612	0.628561
	09/01/2000	0.640997	0.17688	0.871779	0.345542
	10/01/2000	0.468783	0.218556	0.158336	0.987228
01/01/2000 21:30	05/01/2000	0.593771	0.506665	0.664236	0.094826
	06/01/2000	0.392837	0.402604	0.358911	0.831175
	07/01/2000	0.811633	0.23189	0.068549	0.602519
	08/01/2000	0.378539	0.907786	0.68405	0.132635
	09/01/2000	0.068982	0.477773	0.283442	0.547764
	10/01/2000	0.303974	0.48517	0.971859	0.823869

The data is sorted according to the settlement date (ascending). Also there are 3 forecasts a day as you can see in the LHS column (note that these times are not static and can vary by a few minutes).

What I would like to have is a column at the end of the table, containing a list (per each row or settlement date) which shows all of the forecast timestamps -> which are between the settlement date for that row and the settlement date - 14 days.

So for example, if the row's settlement date was the 15th January; there would be a list containing all of the 14 * 3 time stamps = 42 time stamps (1 Jan 09:30, 1 Jan 14:30, 1 Jan 21:30, ... 14 Jan 21:30 ) between the 1st and 15th January.

I'm thinking this could be done by a "vlookup" in to a column of all distinct time stamps (which I've been able to create), I'm just unsure of how to create this additional column.

Once I've created this column of lists - I would then like to do another "vlookup" for each time stamp inside that list and retrieve the values for Period 1, Period 2, ..., Period n.

Any help would be greatly appreciated!

 

[p45cal]

In the forecast timestamp column, are blank cells taken to be the same as the value above the blank cells or remain as blank (and therefore ignored)?
It would be much better if you were to attach a file containing more data so that we can test properly for 14 days' results and so that we know what's really going on with the Period n and … columns in order to record/write proper M code.

 

[ak123]

hi pascal,

sure, the blank cells should be the same as the value above please. i'll post below some more context about what i'm trying to solve.

I'm envisaging a table that looks like below which contains a new column of lists.

forecast timestamp	settlement dt - 14	settlement date	Period 1	Period 2	…	Period n	timestamps within date range
10/01/2000 09:30	01/01/2000	15/01/2000	0.694076	0.836412	0.47013	0.264499	list
	02/01/2000	16/01/2000	0.8036	0.033309	0.460603	0.097421	list
	03/01/2000	17/01/2000	0.279129	0.761234	0.829295	0.582785	list
	04/01/2000	18/01/2000	0.663147	0.509303	0.798564	0.5087	list
	05/01/2000	19/01/2000	0.699019	0.301492	0.735226	0.385019	list
	06/01/2000	20/01/2000	0.669395	0.29449	0.610192	0.442273	list
10/01/2000 13:30	01/01/2000	15/01/2000	0.132674	0.078859	0.351541	0.539105	list
	02/01/2000	16/01/2000	0.513159	0.129552	0.386761	0.986358	list
	03/01/2000	17/01/2000	0.757451	0.903553	0.168224	0.148062	list
	04/01/2000	18/01/2000	0.296439	0.828834	0.234968	0.263399	list
	05/01/2000	19/01/2000	0.010451	0.480623	0.135564	0.41822	list
	06/01/2000	20/01/2000	0.053052	0.061175	0.754388	0.03808	list
10/01/2000 21:30	01/01/2000	15/01/2000	0.460326	0.242416	0.723805	0.072719	list
	02/01/2000	16/01/2000	0.354841	0.342865	0.261104	0.238647	list
	03/01/2000	17/01/2000	0.205888	0.116011	0.616883	0.008302	list
	04/01/2000	18/01/2000	0.467615	0.705912	0.487325	0.946343	list
	05/01/2000	19/01/2000	0.20522	0.154813	0.79244	0.255762	list
	06/01/2000	20/01/2000	0.439527	0.063046	0.230321	0.414235	list

If we expanded the list on the first row (where settlement date = 15/1/2000), we would end up with a list of all of the timestamps (from the 1st column) that lie between settlement dt - 14 and settlement date.
i.e. this is what it should look like after expanding the list in the first row:

01/01/2000 09:30

01/01/2000 13:30

01/01/2000 21:30

…

14/01/2000 21:30

upon checking the condition i.e. that the timestamp should be >= settlement date - 14d AND timestamp <= settlement date, the list should return all timestamps that satisfy this condition... from the 1st column - it should not be an automated date function that returns a sequence of dates between a start / end date.

this is because the hh:mm portion of the datetime is not constant i.e. it does not always follow the pattern of 09:30 -> 13:30 -> 21:30. it can vary by up to 20 minutes for example...

once it's possible to create the column of lists above, i want to isolate only the columns for settlement date, timestamps within the date range and the values (like so):

settlement date	timestamps within date range	Period 1	Period 2	…	Period n
15/01/2000	01/01/2000 09:30	-	-	-	-
	01/01/2000 13:30	-	-	-	-
	01/01/2000 21:30	-	-	-	-
	…	-	-	-	-
	14/01/2000 21:30	-	-	-	-
16/01/2000	02/01/2000 09:30	-	-	-	-
	02/01/2000 13:30	-	-	-	-
	02/01/2000 21:30	-	-	-	-
	…	-	-	-	-
	15/01/2000 21:30	-	-	-	-
…	…

i will retrieve the values for the "period 1, period 2, ..." columns by doing a merge on the first table.

thank you for looking into this!!

 

[p45cal]

Not using a settlement dt - 14 column but sticking with your table in msg#1, first land the table into Power Query with a query named Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"forecast timestamp", type datetime}, {"settlement date", type date}}))
in
    #"Changed Type"

Then another query:

let
    Source = Table1,
    CTSL = List.Buffer(Source[forecast timestamp]),
    #"Added Custom2" = Table.AddColumn(Source, "timestamps within date range", each let sd = [settlement date] in List.Select(CTSL,each _ <= DateTime.From(sd) and _ >= DateTime.From(Date.AddDays(sd,-14))))
in
    #"Added Custom2"

should give you your lists.
(CTSL=Complete Time Stamp List)
It would be so much easier to attach a file here, easier even than pasting a table, then I could give you a working example.

 

[ak123]

tyvm pascal!!

it's so close.
the only thing now is, when i click on one of the lists - it returns duplicates.
is there a way to filter the list of timestamps so that it only shows unique values?

 

[p45cal]

You could List.Distinct here:

= Table.AddColumn(Source, "timestamps within date range", each let sd = [settlement date] in List.Distinct(List.Select(CTSL,each _ <= DateTime.From(sd) and _ >= DateTime.From(Date.AddDays(sd,-15)))))

but it would be more efficient to do it here:

CTSL = List.Buffer(List.Distinct(Source[forecast timestamp])),

 

[ak123]

Dude, you're a life saver.