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! 

[v-jingzhang]

Hi @ak123 

This is my solution. Hope it would be helpful. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAwMDBQNLK2MDJR0lA1OYkFKsTrSSAkjIDFPIHFPIAlPIEkPI0ABFCMkBhsYD7AAjQ7o7IBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"forecast timestamp" = _t, #"settlement date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"forecast timestamp", type datetime}, {"settlement date", type date}}, "en-GB"),
    
    timeStampList = {#time(9,30,0), #time(13,30,0), #time(21,30,0)},
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({List.Sort(List.Repeat(List.Dates([settlement date], 14, -1 * #duration(1, 0, 0, 0)),3)), List.Repeat(timeStampList, 14)})),
    #"Transform Custom" = Table.TransformColumns(#"Added Custom",{{"Custom", each List.Transform(_, each Text.Combine(List.Transform(_, Text.From), " "))}})
in
    #"Transform Custom"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ak123]

Jing, thank you v much for your reply!

That was real close to what I'm looking for! I forgot to mention 1 other detail.

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 10 to 20 minutes...

once again, thank you v much for your help. fyi i'm posting some more context around what i'm trying to do below (if useful).

------------------

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 10 to 20 minutes...

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!!