Advanced sorting/calculation

Hi JoMo1 ,

You need to create a custom function to calculate the working days in Power Query first, called 'Networkdays'

(StartDate as date, EndDate as date) as number =>
let
    DateList = List.Dates(StartDate,Number.From(EndDate - StartDate),#duration(1,0,0,0)),
    RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) < 5),
    CountDays = List.Count(RemoveWeekends) 
in
    CountDays

Then the data source query would be like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fctBCsAgDETRu7i2jBkl6inavXj/a1SEUrPJKh/yZoxwP5dqu5h6iEEoaR0mCipI0zM6uoPFtKuFyKYdvd6QatrVBWymDy2at6bkjxD/dLfPC/TMOV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client Code" = _t, #"Product Num" = _t, #"Start Billing Date" = _t, #"End Billing Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Code", type text}, {"Product Num", Int64.Type}, {"Start Billing Date", type date}, {"End Billing Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Client Code", "Product Num"}, 
            {
                {"Data", each 
                let tab = Table.AddIndexColumn(_,"Index",1,1)
                in Table.AddColumn(
                    tab,"Previous Row",
                    (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[End Billing Date]
                                    otherwise null 
                ), 
                type table [Client Code=nullable text, Product Num=nullable number, Start Billing Date=nullable date, End Billing Date=nullable date, Index = nullable number, Previous Row=nullable date]
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Start Billing Date", "End Billing Date", "Previous Row"}, {"Start Billing Date", "End Billing Date", "Previous Row"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Data", "Days since Last Billing", each try Networkdays([Previous Row], [Start Billing Date]) otherwise null,type date),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Previous Row"})
in
    #"Removed Columns"

You can get the expected result now:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.