cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## New index column for working days in a month

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

1 ACCEPTED SOLUTION
Super User
``````let
Source = your_table,
[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",
GroupKind.Local,
(s, c) => Number.From(Date.StartOfMonth(s) <> Date.StartOfMonth(c))
),
combine = Table.Combine(group[dates])
in
combine``````
2 REPLIES 2
Super User
``````let
Source = your_table,
[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",
GroupKind.Local,
(s, c) => Number.From(Date.StartOfMonth(s) <> Date.StartOfMonth(c))
),
combine = Table.Combine(group[dates])
in
combine``````
Helper III

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