Work Day of the Month in Query Editor

[adam_mac]

Hi, i have created a calculated column in my calendar table that shows the working day of the month, leaving the weekend days as blank (code below). 

I cant seem to work out how to replicate this in Query editor however, any ideas?

WorkDayNumber = 
VAR CurrentMonth = 'Calendar'[MonthYear]
VAR MonthTable = FILTER(ALL('Calendar'),'Calendar'[WeekdayWeekend]="Weekday" && 'Calendar'[MonthYear]= CurrentMonth)

Return 
IF('Calendar'[WeekdayWeekend]="Weekend", 
BLANK(), 
RANKX(MonthTable, CALCULATE(AVERAGE('Calendar'[DayOfMonth])),,ASC))

[edhans · Accepted solution]

See this code @adam_mac . Generally this wouldn't be super performant, but date tables rarely have more than a few thousand records and this works just fine for that purpose.

let
    Source = {Number.From(#date(2021,1,1))..Number.From(#date(2021,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Added IsWorkday" = Table.AddColumn(#"Inserted Day", "IsWorkDay", each Date.DayOfWeek([Date],Day.Monday) < 5, Logical.Type),
    #"Grouped Rows" = 
        Table.Group(
            #"Added IsWorkday", 
            {"Year", "Month", "IsWorkDay"},
            {
                
                {
                    "All Rows", 
                    each
                        let
                            varIsWorkDay = [IsWorkDay]{0}
                        in
                        if varIsWorkDay = true then Table.AddIndexColumn(_,"Workday of Month", 1, 1)
                        else Table.AddColumn(_, "Workday of Month", each null),
                    type table [Date=nullable date, Year=Int64.Type, Month=Int64,Type, Day=Int64.Type, IsWorkDay=logical, Workday of Month = Int64.Type]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Day", "Workday of Month"}, {"Date", "Day", "Workday of Month"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All Rows",{{"Date", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Date", "Year", "Month", "IsWorkDay", "Workday of Month"})
in
    #"Reordered Columns"

This is what it returns for 2021:

Note that I sorted and reordered the columns. That is 100% unnecessary for the result to load to the data model as sorting is irrelevant and column order is shown alphabetically. I just did it for the screenshot so you could see the weekends being ignored.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[adam_mac]

this worked perfectly @edhans ! Thanks for the assist 

[edhans]

Glad it was useful for you @adam_mac 

[JW_van_Holst]

Copy Calendar table,

Filter “Weekend” in the WeekWeekend column

Groupby MonthYear column, aggretating all data

Add column Table.AddIndexColumn([data], "Workday#", 1,1) ([data] being the aggregation of the grouping)

Expand and remove all columns but date and WorkdayNo

Left out join Calendar with copy

Et volia