Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Ttaylor9870
Helper III
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...

 

DateYearMonthDayIndex (Column that would be the solution to my problem above)
01/06/20242024JuneSat 
02/06/20242024JuneSun 
03/06/20242024JuneMon1
04/06/20242024JuneTues2
05/06/20242024JuneWed3
01/07/20242024JulyMon1
01/07/20242024JulyTues2
01/07/20242024JulyWed3
01/07/20242024JulyThurs4
01/07/20242024JulyFri5
01/07/20242024JulySat 
01/07/20242024JulySun 
01/07/20242024JulyMon6

 

Any help would be greately appriciated!

 

Many Thanks,

 

Taylor

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors