Forum Discussion
Return a list based on multiple conditions in Power Query
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!
2 Replies
Hi Anonymous
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.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!!
