Generate List of dates from specific date and only for 1 month

[jaryszek]

hi Guys,

my input table is :
15.03.2024

-1

-2

-3

-4

-5

and i created a code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    StartingDateValue = Table.ColumnNames(Source){0},
    Dates = Table.Column(Source, StartingDateValue),
    StartingDate = Date.FromText(StartingDateValue),
    
    GetPreviousMonday = (LastDLastDayOfWeekCheck) =>
        let
            DayOfWeekLastDay = Date.DayOfWeek(LastDLastDayOfWeekCheck),
            DaysToSubtract = if DayOfWeekLastDay = Date.DayOfWeek(Date.EndOfWeek(Date.AddDays(Date.AddWeeks(LastDLastDayOfWeekCheck, -1), Day.Monday))) then 6 else 7 - DayOfWeekLastDay,
            PreviousMonday = LastDLastDayOfWeekCheck - Duration.From(DaysToSubtract)
        in
            PreviousMonday,

    GetLastDayOfWeek = (StartingDate, NumberOfWeeksToSubtract) =>
        let 
            LastDayOfWeek = Date.EndOfWeek(Date.AddDays(Date.AddWeeks(Date.EndOfMonth(StartingDate), NumberOfWeeksToSubtract), Day.Monday))
        in 
            LastDayOfWeek,    

    AddListTransform = List.Transform(Dates, each let 
        LastWeekDay = GetLastDayOfWeek(StartingDate, _),
        MondayWeek = GetPreviousMonday(LastWeekDay),
        GeneratedList = List.Generate(
            () => [Counter = 0, WeekDate = MondayWeek, TempDate = MondayWeek],
            each [WeekDate] <= LastWeekDay,
            each [
                Counter = [Counter] + 1,
                WeekDate = Date.AddDays([WeekDate], 1)
            ],
            each [WeekDate]
        )
    in GeneratedList)
in
    AddListTransform

which is generating List of List of dates for each corresponding record (for -1, -2 and so on). 

Now i want to replace all dates not within March (15.03 is a March) for nulls. 

So this means that last list :

26.02.2024

27.02.2024

28.02.2024

29.02.2024

01.03.2024

02.03.2024

03.03.2024

will have nulls where 02 is (February). 

I thought to modify List.Generate function like :

WeekDate = if Date.Month(StartingDate) = Date.Month(Date.AddDays([WeekDate], 1)) then Date.AddDays([WeekDate], 1) else null

but i am getting errors and producing only one value in first and last list. 

Can anybody help ?
How to transform List inside list.generate ? It is possible or additional step is required?

Output will be a table from List of Lists to get: 

Mon	Tue	Wed	Thu	Fri	Sat	Sun
25.03.2024	26.03.2024	27.03.2024	28.03.2024	29.03.2024	30.03.2024	31.03.2024
18.03.2024	19.03.2024	20.03.2024	21.03.2024	22.03.2024	23.03.2024	24.03.2024
11.03.2024	12.03.2024	13.03.2024	14.03.2024	15.03.2024	16.03.2024	17.03.2024
04.03.2024	05.03.2024	06.03.2024	07.03.2024	08.03.2024	09.03.2024	10.03.2024
				01.03.2024	02.03.2024	03.03.2024

So in another steps i will use Table.FromList and combine each table into 1 result. 

Best,
Jacek

[jaryszek]

Wow great solution!!!

[m_dekorte · Accepted solution]

Hi @jaryszek 

Maybe a more simple approach could be something like this

(Date as date, OrderType as number ) as table =>
let
    Source = Date,
    Weekdays = List.Transform( List.Dates(#date(2024, 1, 1), 7, Duration.From(1)), each Text.Start( Date.DayOfWeekName(_, "en-US"), 3)),
    ListDates = List.Repeat( {null}, Date.DayOfWeek(Date.StartOfMonth(Source))) & List.Dates( Date.StartOfMonth(Source), Date.Day( Date.EndOfMonth(Source)), Duration.From(1) ),
    ListSplit = List.Transform( List.Split( ListDates, 7), each if List.Count(_) <7 then _ & List.Repeat({null}, 7-List.Count(_)) else _),
    Result = if OrderType = 1 
        then Table.ReverseRows( Table.FromRows( ListSplit, Weekdays ))
        else Table.FromRows( ListSplit, Weekdays )
in
    Result

This custom function takes a "Date"; value (or you can update the code to accept a text and convert that).
It also takes an "OrderType"; if that equals 1, then the order is descending, otherwise it is ascending.

To illustrate:

and the other way around (notice the second argument)

I hope this is helpful