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