New index column for working days in a month

[Ttaylor9870]

Hey Experts, Hope you're well!

I'd like to add in an index column which indexes each working day in a given month & year. Below is an example of what I'm trying to achieve within my date table...

Date	Year	Month	Day	Index (Column that would be the solution to my problem above)
01/06/2024	2024	June	Sat
02/06/2024	2024	June	Sun
03/06/2024	2024	June	Mon	1
04/06/2024	2024	June	Tues	2
05/06/2024	2024	June	Wed	3
01/07/2024	2024	July	Mon	1
01/07/2024	2024	July	Tues	2
01/07/2024	2024	July	Wed	3
01/07/2024	2024	July	Thurs	4
01/07/2024	2024	July	Fri	5
01/07/2024	2024	July	Sat
01/07/2024	2024	July	Sun
01/07/2024	2024	July	Mon	6

Any help would be greately appriciated!

Many Thanks,

Taylor

[AlienSx · Accepted solution]

let
    Source = your_table,
    add_index = (tbl) => 
        [rows = List.Buffer(Table.ToRecords(tbl)),
        g = List.Generate(
            () => 
                [i = 0, 
                r = rows{0},
                wd = Date.DayOfWeek(r[Date], Day.Monday) <= 4,
                index = if Date.DayOfWeek(r[Date], Day.Monday) <= 4 then 1 else null],
            (x) => x[i] < List.Count(rows),
            (x) => 
                [i = x[i] + 1, 
                r = rows{i}, 
                wd = Date.DayOfWeek(r[Date], Day.Monday) <= 4,
                index = List.Sum({x[index], Number.From(wd)})],
            (x) => x[r] & [index = if x[wd] then x[index] else null]
        ),
        to_table = Table.FromRecords(g)][to_table],
    sort = Table.Sort(Source, "Date"),
    group = Table.Group(
        sort, 
        "Date",
        {"dates", add_index},
        GroupKind.Local,
        (s, c) => Number.From(Date.StartOfMonth(s) <> Date.StartOfMonth(c))
    ),
    combine = Table.Combine(group[dates])
in
    combine

[Ttaylor9870]

Brillaint 10/10 this works a charm, thank you very much!!