Custom Column showing average based on multiple conditions

Hi shaunparsons66 ,

Please refer to my steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVXSUXJJBFNhmcWZJflFxUqxOhApBUOgqJGBkZG+oT6IaWiKkDKCSRnrA4WBbCQ5Y5ichb4hSJ8JRK4YJGeCMBKkzdxAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site", type text}, {"Date", type date}, {"Visitors", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.StartOfMonth([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(List.Transform(
{Number.From(Date.StartOfMonth([Date]))..Number.From(Date.EndOfMonth([Date]))},
each if  Date.DayOfWeek(Date.From(_),0) =0 or Date.DayOfWeek(Date.From(_),0) =6 then 0 else 1))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Visitors]/[Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "year", each Date.Year([#"Date"])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each Date.Month([#"Date"])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"Custom.3", "month"}}),
    #"Changed Type2" = Table.AddColumn(#"Renamed Columns", "res", each List.Sum(Table.SelectRows(#"Renamed Columns", (x)=>x[year]=[year] and x[month]=[month])[Visitors])),
    #"Added Custom5" = Table.AddColumn(#"Changed Type2", "Custom.3", each [res]/[Custom.1]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom5",{{"Custom.1", "working days"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Custom.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"res", "summonth value"}, {"Custom", "firstday"}})
in
    #"Renamed Columns2"

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.