Create list of non-consecutive dates using M...

[Matty]

Hi again,

Next problem: how to create a list of non-consecutive dates in Power Query.

Source data as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Dates[/TH]
[/TR]
[TR]
[TD]11/12/2017[/TD]
[/TR]
[TR]
[TD]18/12/2017[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[/TR]
[TR]
[TD]18/12/2017[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

Table (list) to be generated:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Dates[/TH]
[/TR]
[TR]
[TD]11/12/2017[/TD]
[/TR]
[TR]
[TD]18/12/2017[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

In other words, the earliest and latest dates needs to be identified from the source data, and then a list of dates 7 days apart should be generated (the dates are always a Monday).

Anyone have any ideas how this could be achieved?

Cheers,

Matty

 

[MarcelBeug]

That would be:

= List.Dates(List.Min(Source),1+Duration.Days(List.Max(Source)-List.Min(Source))/7,#duration(7,0,0,0))

 

[gazpage]

Not in front of a PC, but if you pull your dates in as one column table and right click at the top you can select drill down and convert it to a list. You can then use List.Max and List.Min to find the highest and smallest date.

Once you have those you can use List.Generate, the first example on the power query website can be adapted. Bear in mind that dates are just an integer under the hood, so you can add 7 like you would in excel.

 

[MarcelBeug]

@gazpage: unlike Excel, in Power Query numbers and dates are incompatible.
It is possible to add a duration to a date, and it is possible to concatenate dates and times ([date] & [time]), resulting in date/time values, but you can't add a number to a date.

 

[Matty]

Thanks Marcel - much appreciated.

Cheers,

Matty