Scaling a complex query approach to run multiple scenarios?

Long response incoming 😬. Given length of explanations, I'm posting a Cacls2 query first with the requested "At Least" Binomial Calculation.

"At Least" Binomial Calculation 

let
    Source = Data,
    GroupDept = 
    Table.Group( 
        Source, 
        {"Department"}, 
        {{"Data", each _, type table [Department=nullable text, Success=logical]}}
    ),
    PerformCalcs = 
    let 
        bfunc = (x as number, n as number, P as number) => 
        // b(x; n, P) = nCx * P^x * (1 – P)^(n – x)
            Number.Combinations( n, x ) * Number.Power(P,x) * Number.Power(1 - P, n - x),
        ScenarioSchema = type table [
            x=Int64.Type,
            Discrete=number,
            Cumulative=number, 
            At Least = number
        ]
    in
    Table.AddColumn(
        GroupDept, "Calcs", 
        each let
            n = Table.RowCount( [Data] ),
            Successes = Table.RowCount( Table.SelectRows( [Data], each [Success] ) ),
            P = Successes / n,
            xList = List.Numbers(0,n + 1),
            Discrete = List.Generate(
                ()=> 0,
                each _ <= n,
                each _ + 1,
                each bfunc(_,n,P)
            ),
            DiscreteBuffed = List.Buffer(Discrete),
            Cumulative = List.Generate(
                ()=> 0,
                each _ <= n,
                each _ + 1,
                each List.Sum( List.FirstN( DiscreteBuffed, _ + 1 ) )
            ),
            CumulativeBuffed = List.Buffer(Cumulative),
            AtLeast = List.Generate(
                ()=> 0,
                each _ <= n,
                each _ + 1,
                each 1 - (CumulativeBuffed{_} - DiscreteBuffed{_})
            ),
            ScenariosTable = #table( ScenarioSchema, List.Zip({xList,Discrete,Cumulative,AtLeast}) )
        in
            [n=n,P=P,Scenarios=ScenariosTable],
        type [n=Int64.Type,P=number,Scenarios=ScenarioSchema]
    ),
    RemoveDataCol = Table.RemoveColumns(PerformCalcs,{"Data"})
in
    RemoveDataCol

Switch out Calcs with the new Calcs2 above. The Metadata query won't need any changes, but you'll have to redo expand step in Scenarios.

Can you break down your Calcs logic/approach for me (comments apply to previous post, not above solution, but techniques are the same)

1) We group on Department so that we have one row per department, and a [Data] column that houses all data per each Department. When we add a custom column without expanding [Data], we are able to reference the whole dataset for each Department with a reference to [Data]

2) Overall, our add custom column step is creating a record column. We make a few calculations that we put into a record that we can expand in subsequent steps. let/in is used to define sub-outputs for re-use and readability. Simple examples that show these fundamentals:

// Add custom column to simple tabble without using let/in
Table.AddColumn( 
    //create single column table; col name is [Num] of type Int64; vals are 0-3
    #table( type table [Num=Int64.Type], {{0},{1},{2},{3}}),
    "RecordColumn",
    each [
        TextRec = Text.From([Num]) & " Text!",
        PowerRec = Number.Power( 2, [Num] ),
        StaticRec = 90.01
    ],
    //here we specify the column type; not strictly required but a good practice to include
    type [TextRec=text, PowerRec = Int64.Type, StaticRec = Decimal.Type]
)

// Add same custom column to table using let/in
Table.AddColumn( 
    //create single column table; col name is [Num] of type Int64; vals are 0-3
    #table( type table [Num=Int64.Type], {{0},{1},{2},{3}}),
    "RecordColumn",
    each let
        textval = Text.From([Num]) & " Text!",
        powerval = Number.Power( 2, [Num] ),
        staticval = 90.01
    in
        [TextRec = textval, PowerRec = powerval, StaticRec = staticval],
    //here we specify the column type; not strictly required but a good practice to include
    type [TextRec=text, PowerRec = Int64.Type, StaticRec = Decimal.Type]
)

FYI Equivalent (with let/in) using UI (minus setting the column type, which isn't available in UI):

3) The record we are returning per row includes [n] and [P], which are similar to the calculations in the above simple examples (except the field we are referencing is a table instead of a number as in the example). However, the value for [Scenarios] is going to be a table of x,b pairings. E.g.:

Breaking down how we achieve this:

a) First, it's important to note that any time we want to build out custom data through iteration or recursion, List.Generate is by and far the best option performance-wise, so it is very useful to get familiar with it. ScenariosList's List.Generate in plain English:

i) while the field [x] <= n...

ii) start the first list item with a record with field [x] eqal to 0, and field [b] equal to bfunc(0,n,P)

iii) check against 'while' criteria by adding +1 to latest value of [x] field and calculating next bfunc and assigning to [b] field

iv) (implicit because we don't specify last 4th argument) because no output is specified, use the value (i.e. our record with x and b fields) that was used to check against criteria

Quick correction note: I fixed this in my previous post, but please note that the x argument for bfunc in the 3rd argument of List.Generate should be [x]+1, not [x]. I also switched to simpler syntax rather than Record.TransformFields 

b) Just to double-check, let's look at the actual list getting generated from the internal ScenariosList, which is where most of the work is occurring. Below, we are modifying the Table.AddColumn portion of the PerformCalcs step to output ScenariosList instead of ScenariosTable (recommend duplicating Calcs query and modifying the duplicate):

Table.AddColumn(
    GroupDept, "Calcs", 
    each let
        n = Table.RowCount( [Data] ),
        Successes = Table.RowCount( Table.SelectRows( [Data], each [Success] ) ),
        P = Successes / n,
        ScenariosList = List.Generate(
            ()=> [x=0, b=bfunc(0,n,P)],
            each [x] <= n,
            each [x=[x]+1,b=bfunc([x]+1,n,P)]
        ),
        ScenariosTable = Table.FromRecords(ScenariosList,ScenarioSchema)
    in
        ScenariosList, //OLD CODE: [n=n,P=P,Scenarios=ScenariosTable],
    type list //OLD CODE: type [n=Int64.Type,P=number,Scenarios=ScenarioSchema]
)

When we inspect the output, we see that we are indeed generating a list of records where each record has an x and b field.

c) There is a function built specifically to convert a list of records into a table: Table.FromRecords. Some quick examples to show how it works:

let
    Source = 
    {
        [Field1 = "val_1", Field2 = 1],
        [Field1 = "val_2", Field2= null],
        [Field1 = "val_3", Field2 = 3, Field3 = Number.Random()],
        [Field1 = "val_4"],
        [Field1 = "val_5", Field2 = 5]
    },

    //All below steps are referenceing Source, not previous step
    Simple = Table.FromRecords( Source ),
    UseNullForMissing = Table.FromRecords( Source, null, MissingField.UseNull ),
    WithFieldNames = Table.FromRecords( Source, {"Field1","Field2"} ),
    WithTableSchema = 
    Table.FromRecords( 
        Source, 
        type table [Field1=text,Field2=Int64.Type] 
    ),
    AllTogether = 
    Table.FromRecords( 
        Source, 
        type table [Field1=nullable text,Field2=nullable Int64.Type], 
        MissingField.UseNull 
    ),
    WithAllFields = 
    Table.FromRecords( 
        Source, 
        type table [
            Field1=nullable text,
            Field2=nullable Int64.Type,
            Field3=nullable Decimal.Type
        ], 
        MissingField.UseNull 
    )
in
    WithAllFields

So, as shown from the above query if you paste into advanced editor and look at variations of use, our ScenariosTable internal step is converting the list of records created from ScenariosList into a well-typed table in our Scenarios field, which can be expanded in one step (after expanding the record of which Scenarios is a field). We are not using MissingField.UseNull because we are constructing the list and don't expect any missing fields, so would want it to error if somehow a field didn't show. And to be clear, we could alternatively i) output the lists as-is in custom column, ii) expand list column to new rows (now each row is a record), iii) expand the record column to show fields, and finally iv) transform column types. There isn't a performance difference, so it comes down to preference.