Generate table of historic summaries in PQ

jf114

New Member
Joined
Sep 19, 2016
Messages
3
Hi

I have a table of events that each have a start and an end date. I want to generate a summary table in PQ with two columns:

1. A date ("weekdate") for every week in the last year, so List.Dates(Date.From(DateTime.FixedLocalNow()-#duration(365,0,0,0)),52,#duration(7,0,0,0))
2. A sum of all the durations that the events have been open for on each of these dates (0 for events not started, end-start for events ended, weekdate-start for those in progress)

I'm sure that there is a clever way of doing this, but my PQ knowledge doesn't quite go that far. I can figure out the duration calculation, but I'm not sure how to do the generation of intermediate duration data structure from the source table and date list, prior to summarising into the result. Or maybe this isn't the way to do it?

Any pointers would be very much appreciated.

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Having difficulties to understand what you want here. Please provide tables before and after with all relevant cases.
 
Upvote 0
Hi Imke

Thanks for the reply. Sorry, not so clear. Hopefully the tables below help. The duration calculation is a little different to what I said above, though I can figure that piece out.

[TABLE="width: 798"]
<tbody>[TR]
[TD="class: xl69, width: 72, bgcolor: transparent"]1. Source
[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 75, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 651, bgcolor: transparent, colspan: 10"]2. Intermediate calculation of durations on a set of dates generated by List.Date(…) 52 weeks going back from today
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]event#
[/TD]
[TD="class: xl66, bgcolor: transparent"]start
[/TD]
[TD="class: xl66, bgcolor: transparent"]end
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]date
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]22-Sep-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]29-Sep-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]06-Oct-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]13-Oct-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20-Oct-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]27-Oct-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]03-Nov-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10-Nov-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]17-Nov-15
[/TD]
[TD="class: xl66, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]#101
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]02-Apr-14
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]30-Apr-14
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]#102
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]15-Jul-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]13-Apr-16
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]69
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]76
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]97
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]111
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]118
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]125
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]#103
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]01-Sep-16
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]#104
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]01-Aug-15
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]52
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]66
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]73
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]108
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]#105
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]26-Oct-15
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]13-Nov-15
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]total
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]121
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]135
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]149
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]163
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]177
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]192
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]213
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]234
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]233
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, colspan: 12"]Duration calculation pseudo code (less the type handling) is = if ( [end] <> "" and [date] > [end] ) or [date] < [start] then 0 else [date] - [start]
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]3. Result:
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]date
[/TD]
[TD="class: xl67, bgcolor: transparent"]total
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]22-Sep-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]121
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]29-Sep-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]135
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]06-Oct-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]149
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]13-Oct-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]163
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]20-Oct-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]177
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]27-Oct-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]192
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]03-Nov-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]213
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10-Nov-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]234
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]17-Nov-15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]233
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
There are probably a couple of approaches that replicate your pseude-code somehow. But I don't really like it.
So enclosed you find a different approach that breaks the logic up into easy-to-follow steps working on the technique that every day gets its own row:

Code:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxLDoAgEEPvglsyaYePehbDDYg77y8jYFi+9rXX5TaCzjuoIIqCsUHAD8V/iraUSbBbmgzCUPJUgr1QcPbUu/updZZxlMfcr6WxZiGWc7JDKS8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"event#" = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}}),
    EndOrToday = Table.AddColumn(#"Changed Type", "EndOrToday", each if [end]=null then Date.From(DateTime.LocalNow()) else [end]),
    ListOfDates = Table.AddColumn(EndOrToday, "Dates", each {Number.From([start])..Number.From([EndOrToday])}),
    #"Expanded Dates1" = Table.ExpandListColumn(ListOfDates, "Dates"),
    CumNoOfDays = Table.AddColumn(#"Expanded Dates1", "CumNoOfDays", each [Dates]-Number.From([start])+1),
    ChangeToDateFormat = Table.TransformColumnTypes(CumNoOfDays,{{"Dates", type date}}),
    StartOfWeek = Table.AddColumn(ChangeToDateFormat, "StartOfWeek", each Date.StartOfWeek(Date.AddDays([Dates], -1))),
    NoOfDaysPerWeekAndEvent = Table.Group(StartOfWeek, {"event#", "StartOfWeek"}, {{"NoOfDays", each List.Max([CumNoOfDays]), type number}}),
    GroupOnWeekNo = Table.Group(NoOfDaysPerWeekAndEvent, {"StartOfWeek"}, {{"Result", each List.Sum([NoOfDays]), type number}})
in
    GroupOnWeekNo

This part in step "StartOfWeek" enables you to adjust your weekday of the Start.OfWeek: Date.AddDays([Dates], -1
This technique let's every "last-days-count" appear in the last intervall (unlike your example above).
 
Upvote 0
Thank you very much for your help Imke. I'm glad I asked because I would never have figured that out :)

Just one question - seeing as the date in the result is the start of the week, shouldn't NoOfDaysPerWeekAndEvent summarize on the minimum value, as maximum would show the durations for the end of that week?
 
Upvote 0
I wouldn't think so: Consider "StartOfWeek" just as the name of the week in question.

In step "StartOfWeek" you see that all entries of the week that start with the figure in the column are allocated to it. So if you want to check what happened in that whole week, you have to look until the end of the week (Max) and take those values.
 
Upvote 0

Forum statistics

Threads
1,225,661
Messages
6,186,279
Members
453,348
Latest member
newbieBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top